current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question

migrated from stackoverflow.com Jan 27 '12 at 16:12

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 3 down vote

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.

share|improve this answer
    
I like option one better than option two. Option three sounds interesting, but I feel like it might be kind of a hack. Is that a common pattern in the relational theory? –  Joe Snikeris Jan 27 '12 at 23:48
    
Option one are both fine. Just remember that option one means more complex code in the retrieval and less complex code in the maintenance. Option two is the other way around. Option three isn't a common pattern at all. It's taking the data model and turning it sideways a little bit by looking at types/subtypes in a very abstract way. It may not be a hack but it might suffer from being elegant - which can be worse. –  Joel Brown Jan 28 '12 at 1:17
    
Hmm, what do you think about Celko's solution? –  Joe Snikeris Jan 28 '12 at 7:35
    
I really like Celko's method for handling hierarchical data. In fact, if you look through my answers here and on SO you'll find that I've recommended it more than once. To me, Celko's method makes moving up and down the tree easier. It doesn't address your primary concern, which is relating to parents and (optionally) children consistently. You could use Celko's solution as the implementation for option three. That would be a good way to achieve the advantages of that approach. –  Joel Brown Jan 28 '12 at 14:36
    
Bah, I thought I had things figured out, until I saw line 205.3m. Types Creature and Tribal have the same list of subtypes. Now I'm lost again. Thoughts? –  Joe Snikeris Jan 28 '12 at 15:24
up vote 3 down vote accepted

OK, I think I figured out The Right Way to model this. It was staring me right in the face in the form of rule 205.3c:

If a card with multiple card types has one or more subtypes, each subtype is correlated to its appropriate card type.

CREATE TABLE Card_Types (
       card_type varchar(12) PRIMARY KEY,
);

CREATE TABLE Card_Subtypes (
       card_subtype varchar(15) PRIMARY KEY,
);

CREATE TABLE Card_Subtypes_Types (
       card_subtype varchar(15) REFERENCES Card_Subtypes
       card_type varchar(12) REFERENCES Card_Types,
);

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),
       card_subtype varchar(15),
       card_type varchar(12),
       PRIMARY KEY (card_name, card_subtype)
       FOREIGN KEY (card_name, card_type) REFERENCES Card_Characteristics_Types,
       FOREIGN KEY (card_subtype, card_type) REFERENCES Card_Subtypes_Types
);
share|improve this answer
    
It's a little unconventional to use the same columns two or three times in different foreign keys, but I can't think of an example that breaks it nor of a simpler way to achieve all of the same constraints - so +1 for the question and +1 for the answer! –  Joel Brown Jan 30 '12 at 13:58
1  
My only objection is that you don't need the first 3 simple fk REFERENCES in the Card_Characteristics_Subtypes table. The (last) 2 compound foreign keys are enough. –  ypercube Aug 29 '12 at 8:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.