I'm creating a database to store Magic the gathering cards information, one of the things I don't know how to normalize is the card type / sub-type.
Here is my try at explaining how this information works:
Cards have up to 2 types (but there's nothing stopping new cards with 3 types I suppose)
At least 1 type is required per card.
Cards have up to 3 sub-types (same as above.)
No sub-types are required.
So i was going to have a typeID and subtypeID fields in the table with cards information
The only ways I know to store the information in the types and subtypes tables is:
1 - having type1, type2, subtype1, subtype2... fields in my table
2 - having the fields types and subtypes, and storing the information as: "type1,type2", "subtype1,subtype2"
1 is not normalized thus I would like to avoid using it
2 I don't even know if this is considered normalized, but seems like a bad idea.
Neither options 1 or 2 are normalized. They are both examples of "repeating groups" which conflicts with First Normal Form.
Also see my answer to Is storing a delimited list in a database column really that bad?
The proper normalized design is:
Types ---< CardTypes >--- Cards ---< CardSubtypes >--- Subtypes
That's a compact notation that shows a many-to-many table between Types and Cards, and another many-to-many table between Cards and Subtypes. To add multiple types to a given card, just add multiple rows to the CardTypes table. Likewise add multiple rows to CardSubtypes for a card with multiple subtypes.
This is always the normalized way to represent many-to-many relationships.