Database Modelling of Type, Subtype Relation 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?
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.