- User
----------
- user_id
- deck_id
- e-mail




- Deck
----------
- deck_id
- card_id




- Card
-----------
- card_id
- type




- Attributes_to_Card
-----------
- card_id
- attr_id




- Attribute
-----------
- attr_id
- attr_type


Since there could be many attributes to one card, what I've done here is to make a table that holds a card id and it's associated attr id. In your query you'll have to do a query from card table and inner join to the attributes_to_card table and inner join again to the attribute table to get all the attributes for that card.

Someone else may have a cleaner or more refined solution than mine.

(Excuse my magic slang, but it kicked in :D)
I would create one table per card type (monster, instant, enchantment, etc...):

-Monster
-------
-monster_id
-name
-cost
-attack
-defense

-Scorcery
-------
-scorcery_id
-name
-cost

Now it can be a bit tricky to store the actual instants (actions) those monster can do. But this can be done by creating one table per action type. For example, anything that does damage could be inserted into the following table:

-Damage Instants
--------
-instant_id
-dmg
-dmg_type

The last thing is to store the mapping of monster to an instant, which often is a many-to-many relationship: A monster can have instants which are described in the instants table, however several monster could posses the same instant and a monster can have to instant at all.

-Monsters table: monster_id, etc...
-Instants table: instant_id, etc...
-Mapping table: monster_id, instant_id

When retrieving stuff, you can then select a specific monster and ALL instants and ALL permanent effects (and many more) with a single statement and some joins.

Interesting

Quote:Original post by Madhed
Just a nitpick.
Why do I often see the table name repeated as the primary key?

monster
-------
monster_id

instead of:

monster
-------
id

Is there some advantage to it?
I always find it rather confusing to type.

It makes it clear what you're referring to, prevents occasional problems when not fully qualifying column parameters, and makes foreign key relations look a lot better (and easier to spot broken ones).

It seems you are conflating the two completely different concepts of an abstract card (with a name and mechanical details; in MtG they are listed in the Gatherer search engine and the Oracle) and a card instance (belonging to a player and possibly part of a deck; in MtG they are the physical pieces of paper you buy).

The mechanical description of cards might be buried in program code; attributes for card instances might be:

card_instance_id (PK)
card_id (FK to abstract card list)
owner_id (FK to players)
deck_id (FK to decks, optional)
maybe various items about the card's acquisition by that player

The one to many deck <-> card instance relationship obviously replaces the deck <-> abstract card many to many relationship and the corresponding table.

Attributes for abstract cards might be:

card_id (PK)
name
type_id (FK to a table that enumerates card types)
text
illustration (BLOB or asset name)
set (FK to a table that enumerates sets of cards)
any other things that will be displayed to the player in-game

In MtG there's another layer you might not want: different printings and alternate illustrations of the same abstract card. Card instances would reference an abstract "printing" that would contain some details (illustration, set) and reference, in turn, the abstract card definition.