Take the 2-minute tour ×
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.

Is there a name for the following database schema design/pattern? My eventual goal is to find more literature about the subject. Today's cursory net search was too full of generic words to be able pin down the term (if any exists) for this kind of thing:

Fruit (id, farm)

Apple (fruit_id, color)
    [fruit_id => Fruit.id]

Banana (fruit_id, length)
    [fruit_id => Fruit.id]

Orange (fruit_id, is_seedless)
    [fruit_id => Fruit.id]

FruitPack (id, destination)

FruitPackFruits (fruitpack_id, fruit_id, fruit_type)
    [fruit_id => Fruit.id, fruit_type => VARCHAR]

Where fruit_type would be a varchar column filled with values like "Apple, Banana, Orange, Cherry". It's some kind of "poor man's referential integrity". Obviously, one the failures of this kind of design is being able to insert values that don't resolve out to a useful JOIN (ie: there are no cherries to speak of here).

I'm going to take for granted that the schema is a sufficient caricature of some sort of collection of groups of items for the people here.

In closing, the question is... What's this kind of "poor man's referential integrity" called?

PS. I'm not trying to learn about referential integrity. I want to identify this poor design's name and look further into the "let's design a database schema" aspects (ex: pros, cons, opinions, teachings, etc) that have to do with this commonly seen disaster of a schema.

Post-Answer Edit: Added "polymorphic association" tag for future reference.

share|improve this question
2  
I don't see any referential integrity at all. This is an non-normalized column with no integrity. –  BillThor Feb 5 at 20:16
    
Hm, the answer to that likely is "Misunderstanding" to something that could get censored here ;) –  TomTom Feb 5 at 20:20
    
Hence the "poor man's" moniker. Here's another example of such a pattern... a single "log (id, table_name, record_id, timestamp)" table that acts as a sort of tracker for modification-times in various other tables. Strictly speaking, it's got no ref integrity, but, the (table_name, record_id) part is supposed to refer to some record in another table, requiring a join to actually get the full data. –  starlocke Feb 5 at 20:23
    
The closest I can think of is the "supertype/subtype" pattern. Search for that and for "table inheritance". Your schema needs quite a lot of work to be able to enforce integrity constraints though. –  ypercube Feb 5 at 20:38
    
Would it be better for us to call this a kind of "polymorphism" ? –  starlocke Feb 5 at 20:48

2 Answers 2

up vote 3 down vote accepted

Aha! Thanks to @ypercube's comment about "inheritance", I managed to make a logic/context leap and found some concrete examples that give the name "Polymorphic Associations" to this kind of schema design.

Indeed, this is "a thing" to be reckoned with in the DBA world, and, IMHO, this is also something to be hated and even burned with hellfire.

In the "let's log everything" scenario, it's probably good to follow the wisdom of IBM and create a set of 1-to-1 "_history" (appended) tables plus triggers. It seems this would also apply to tables that act as "i18n + L10n" translation storages.

Not sure what to think about the "let's group different types of items" scenario at this point, other than "polymorphic associations is probably a bad idea in the long term". They're extremely unpleasant to trace/follow/understand/query, in my context of an MVC-based application.

share|improve this answer
    
It's ok (even encouraged) to mark your own answer as "the answer". –  Paul White Feb 6 at 8:09

Your design looks a bit like the "supertype/subtype" pattern. Search for that and for "table inheritance". It needs quite a lot of work to be able to enforce integrity constraints though.

You are missing a generic Fruit table (that's the "supertype") and a FruitType table to store the alllowed fruit types:

FruitType 
    fruit_type PK

Fruit
    fruit_type PK, FK -> FruitType (fruit_type)
    fruit_id   PK

Then the 3 (or 4 or more) tables would be (the "subtype" tables):

Apple
    fruit_type 
    fruit_id PK
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)
    CHECK (fruit_type = 'Apple')

Banana 
    fruit_type PK
    fruit_id PK
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)
    CHECK (fruit_type = 'Banana')

Orange
    fruit_type PK
    fruit_id PK
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)
    CHECK (fruit_type = 'Orange')

And any other table can reference the Fruit table:

FruitPack 
    fruitpack_id PK 
    destination

FruitPackFruits 
    fruitpack_id FK -> FruitPack (fruitpack_id)
    fruit_id     
    fruit_type
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)

It doesn't look very nice and one column in every "fruit" table seems redundant as it has one and only one allowed value. And every time you need to add a new fruit (say Cherry), you have to add a row in the table FruitType and a new table (Cherry), similar to the other ones. So, it works better if your design is more or less stable. If you find that you may need to add a new "fruit" every few days or if you have a thousand (or more!) different fruits, it's not the best way.

On the other hand, it enforces integrity and you can't insert cherries into the Bananas or oranges into the Apples.

share|improve this answer
    
Hm. You're right. The "Fruit" table was kind of missing in my original description, however, there was no "FruitType" table involved, thus, "poor man's design" comes into play. I'll go improve that original problem to be a better representation. –  starlocke Feb 5 at 21:33

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.