I'm trying to model the type and subtype relationship described here.
The best approach I could come up with would look like this:
CREATE TABLE Card_Types ( card_type varchar(12) PRIMARY KEY ); CREATE TABLE Card_Subtypes ( card_subtype varchar(15) PRIMARY KEY card_type varchar(12) REFERENCES Card_Types PRIMARY KEY (card_subtype, card_type) ); CREATE TABLE Card_Characteristics ( card_name varchar(141) PRIMARY KEY, -- more to come? ); CREATE TABLE Card_Characteristics_Types ( card_name varchar(141) REFERENCES Card_Characteristics, card_type varchar(12) REFERENCES Card_Types, PRIMARY KEY (card_name, card_type) ); CREATE TABLE Card_Characteristics_Subtypes ( card_name varchar(141) REFERENCES Card_Characteristics, card_subtype varchar(15) REFERENCES Card_Subtypes, PRIMARY KEY (card_name, card_subtype) );
Sample data for Card_Subtypes:
| card_subtype | card_type | |--------------+-----------| | Human | Creature | | Rogue | Creature | | Werewolf | Creature | | Thopter | Creature |
Sample data for Card_Characteristics_Types:
| card_name | card_type | |-------------------------------+-----------| | Kruin Outlaw | Creature | | Ornithopter | Artifact | | Ornithopter | Creature | | Akroma, Angel of Wrath Avatar | Vanguard |
Sample data for Card_Characteristics_Subtypes:
| card_name | card_subtype | |--------------+--------------| | Kruin Outlaw | Human | | Kruin Outlaw | Rogue | | Kruin Outlaw | Werewolf | | Ornithopter | Thopter |
What I don't like about this model is that sometimes you're encoding the type information in two places. If a card has the subtype Human, then it is clearly a creature, so there is no need to store that information in the Card_Characteristics_Types table.
So I guess I could only write to the types table if the card didn't also have a subtype of that type. But that's a constraint that the user would have to uphold.
I know I could accomplish some of this w/ triggers, but I'm trying to stay as close as possible to the relational model. Is there a better way to model this situation?