I'm developing a Pokémon app to learning how work pokemon internally. My question is what is best option to model?

A little context:

A Pokémon has types (Water, Fire, Grass...)

A type has effectiveness and weaknesses.

Example:

1.- Can i model a table with type but how can relation them? 2.- I can use graph teory, but how do i represent it in database?

This development was born out of the same curiosity.

Thanks!

Create table with relation, but i think is not efficient

CodePudding user response：

This is a many-to-many relationship. Multipe types can have the same strength, and a type can have multiple strengths. Many to many relationship require an intermediate bridge table. And in this case, it's a loop back to the same table, as a type is relating to a type. I don't know how to represent ERD symbols in this dialog box, but this should give you the basic picture. Three tables are pictured below, Pokemon, Types and TypeModifiers:

Pokemon                   TypeModifiers
-------                   -------------
[ type id ] --> Types   <--- [base type id
              [type id] <---- modifier type id
                              strength-or-weakness code]

Create a foreign key on TypeModifiers.base_type_id to point to Types.type_id Create another foreign key on TypeModifiers.modifier_type_id to also point to Types.type_id

This will allow you to model any possible relationship between a type and all other types. You could do strengths and weaknesses as separate tables, but it's simpler on queries if you combine them and differentiate with a column value, e.g. 'S' vs. 'W'.