How to organize variable IDs that depend on each other in MySQL

Question

I'm creating a database for all the Magic the Gathering cards... The card table looks like this:

• cardID
• name
• text
• rarityID
• colorID
• editionID
• other columns ...

the same card can appear on multiple editions, and in some unique cases, in a different edition it will have a a different rarity.

The only way I know how to do it would be to create one new card entry for each edition the card appears. But sometimes the same card appears in 10+ editions without any change (at least not on what is relevant for this database), and I would be creating lots of unnecessary entries.

Is there a better solution?

Answer 1

Going on the feedback in the comments, this is what I would recommend (it still might miss changes, but I think it's a fair start). Make sure to capture everything in the domain model! It's easy to change data later .. if you have it.

Cards    |-These are common to the card across all Editions*
 CardId, CardName, DeckColor, ..
 |-PK-|  |-Possible Key?  -|

Editions
 EditionId, EditionName, ReleaseDate, ..
 |-PK   -|  |-Key?   -|

CardEditions        |-These are unique per Card/Edition
 CardId, EditionId, Rarity, Rules, ..
 |-PK           -|
 |-FK-|  |-FK   -|

(*Bill Karwin points out that there are multi-colored cards, arg! To capture that requirement, float a CardsColors relationships much like CardEditions. If the color changes across editions as well, then I just want to cry ..)

I've laid out a simple schema above (arguably not fully normalized). However, for further reading look up "Slowing Changing Data" as there are a few different ways to handle it.

---

Updates for Comment:

How find a card for a specific edition, say we know the card name and edition name:

SELECT * FROM Cards c
JOIN CardEditions ed
  on ed.cardId = c.cardId
JOIN Edition e
  on e.editionId = ed.editionId
WHERE c.cardName = 'Some Awesome Card'
AND e.editionName = 'Ultimate'

Note that if the CardName and EditionName were used as Primary Key (PK) and in the corresponding FK - instead of the surrogate CardId/EditionId - then we could have possibly avoided a join entirely. There is great debate as to which approach is better. I use surrogate keys to avoid compound PK's except in join tables (e.g. CardEditions) as I like to keep queries simpler. However, this still leaves using/having an EditionId surrogate key ..

Possible Key (or "Key?") means that I suspect this field should be treated as a Key as well - that it should have a Unique Constraint (and possibly Index) applied. A relationship can have multiple Keys, where a Key is the set of columns required to uniquely identify a record. The Primary Key is just the "selected" Key that is normally used for this purpose.

Note that this model is not fully normalized, by the very fact that there is more than one Key for a relation; however, I've found it most productive to be consistent with surrogate keys and define secondary constraints. This also is a result of the fact that I like to treat records as entities which drive the model.

Answer 2

Understand that MySQL is a relational database and that to properly leverage its power, you need to relate objects together. So the first thing you need to do is to understand what your different objects are and how they may relate to each other. From your description, you have mentioned at least two objects: cards, and editions.

So I think that means you need two tables to start with, cards and editions, but how do they relate? If there is a one-to-many relationship (i.e. each edition could have many cards, but each card belongs to only one edition) you could just have a foreign key in cards for the edition id. But in this case it seems that you have a many-to-many relationship. This is traditionally accomplished by adding a third table to explain the relationship. So in this case you might add a cards_to_editions table, which would consist solely of two columns, the card id and the edition id. These would both be foreign key keys to their respective tables and would form a compound primary key for the table.

You could also add properties to the cards_to_editions table if there were any properties specific to the relationship itself (like it seems "rarity" might be in this case).