Database Modelling of Type, Subtype Relation

database-design

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?

Best Answer

Important Caveat: You need to look at the link OP provided! It uses the words type and sub-type as jargon in a way which is similar to, but different than what programmers or even data modellers would do.

The issue is that the rules state that if a card has a sub-type, that sub-type must be valid for the given (super)type.

Since not all types have sub-types in this model, you need to have a many to many association between the list of types and individual cards. At the same time, you need to have a many to many association between the list of possible sub-types and individual cards.

You are in a bit of a relational model rock and hard place. It depends on how you want to optimize your model. You can optimize for storage or you can optimize for retrieval.

Optimizing for Storage: You can eliminate the redundancy by linking types without sub-types directly to cards. When you have a type that has sub-types, don't link the type, link the sub-type instead. This avoids redundancy and the potential inconsistency of parents and children contradicting each other. It sucks for data retrieval, however.

Optimizing for Retrieval: You could instead link all types and sub-types (when applicable) to cards and then you have redundancy to manage - either with triggers or some other kind of application logic.

There is one more option you could try, which is a kind of combination approach and which manages to push the complexity into a more rarely used corner of your application. In the third way, you collapse type and sub-type into a single table with an involuted relationship (self-reference). Now when you assign a type or sub-type it automatically implies any parents of that thing.

Given the rules requirement for enforcing membership in the type before allowing membership in a sub-type, I would be inclined to just live with the redundancy. You need a business rule that says you can't have the sub-type if you don't have the type. That's the kind of business rule that needs to be in code anyway.

Related Question