2

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.

  • I think creating associations/association tables here would be a good idea. Have you tried something like that ? – Maximus2012 Jul 18 '13 at 18:42

2 Answers2

0

IMHO: Entity CARDS have all anagraphic information of your cards, but no type. Another entity TYPES have all types (with gerarchical link) In another middles entity CARDS_TYPES you store all links between cards and types.

So if your gerarchical type change you must put these changes in entity TYPES. So, if the number of type of one card grow, you put new rows in the middle entity.

Joe Taras
  • 15,166
  • 7
  • 42
  • 55
0

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.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • It's obvious don't know how I didn't think of it. Good read on your other answer, ty. –  Jul 18 '13 at 18:59