Storing inherited objects in a database

Question

I'm trying to figure out the best way to map inheritance relationships in an object model into a relational database. For example consider the following class structure.

public Class Item 
{
    public String Name{get; set;}
    public int Size {get; set}    
}

public Class Widget:Item
{
    public String Color{get; set;}
}

public Class Doohicky:Item
{
    public String Smell{get; set;}
}

Here's are a few options I'm considering for how to save this structure to a database.

Options 1: Single Table for all item types

Items Table: ItemID, Name, Color, Smell

This sucks as it would require NULL values.

Options 2: Separate tables for each item type

Widgets Table: WidgetID, Name, Color
Doohicky Table: DoohickyID, Name, Smell

This is better, but would be more difficult to list all Items

Options 3: Linked tables

Items Table: ItemID (PK), Name, Size
Widgets Table: WidgetID (PK), ItemID (FK), Color
Doohicky Table: DoohickyID (PK), ItemID (FK), Smell

I think this option is the best, as it prevents me from having Null values in any fields, plus it will make it easier to list all the Items, and/or create a list of a specific type of Item (Widgets or Doohickies).

However, I'm not sure how to create the relationship between the Items table and the Widgets and Doohickies tables. I don't want to end up with row in either table referencing the same ItemID in the Items table.

For example when I add an entry to the Widgets table, how can I ensure that it is linked to a new entry in the Items table with a unique ItemID? Should I instead only track the ItemID rather then separate type specific IDs like WidgetID and DoohickyID, and use it to create a one to one relationships between the Items table and the type specific tables?

Options 4

Items Table: ItemID (PK), Name, Size
Widgets Table: ItemID (PK), Color
Doohicky Table: ItemID (PK), Smell

Answer 1

You're describing Single-Table Inheritance, Concrete Table Inheritance, and Class Table Inheritance.

See the classic Martin Fowler book Patterns of Enterprise Application Architecture for lots of information about them.

One thing you can do to make sure only one sub-type can reference the super-type is to use an ItemType field. A foreign key can reference the columns of a UNIQUE constraint as well as a PRIMARY KEY. So you can add Items.ItemType and make a unique constraint over ItemID, ItemType. The foreign key in each sub-type table references this two-column unique key. And then you can constraint the ItemType in each sub-type table to be a certain value. Then it must match the value in the super-type table, which can only have one value.

Items Table: ItemID (PK), ItemType, Name, Size
  UNIQUE: (ItemID, ItemType)
Widgets Table: ItemID (PK), ItemType, Color
  FK: (ItemID, ItemType)
  CHECK: ItemType=W
Doohicky Table: ItemID (PK), ItemType, Smell
  FK: (ItemID, ItemType)
  CHECK: ItemType=D

Answer 2

If you are designing keeping the current classes in mind, i would prefer your option 3 as the preferred option for the reasons your stated above.

Re your query about :

However, I'm not sure how to create the relationship between the Items table and the
Widgets and Doohickies tables. I don't want to end up with row in either table 
referencing the same ItemID in the Items table. 

Bill's suggestion above is definitely a good option. Something that i hadnt thought of :-) However, i was thinking that its a relationship that you can enforce either in your application code itself or by using an Insert trigger.

My concern however is about trying to simulate the inheritance itself at the DB level.

If the number of child classes is expected to grow over a period of time, then this kind of DB Design could become very difficult to maintain.

In addition to :

Items Table: ItemID (PK), Name, Size        
Widgets Table: WidgetID (PK), ItemID (FK), Color        
Doohicky Table: DoohickyID (PK), ItemID (FK), Smell        

What is the likelyhood of new classes like the following getting added ?

DontDoHicky Table : DoohickyID (PK), ItemID (FK), Smell, **TASTE**        
MidWidget Table : WidgetID (PK), ItemID (FK), Color , **Height**        
MidDoHicky Table : WidgetID (PK), ItemID (FK), **Height**, **Smell**

This is of course the classic OO inheritance problem. If you expect the application to grow like this over a period of time and maybe need to plan for it beforehand, at a DB level, you might prefer go with a more generic approach

Items Table: ItemID (PK), Name, Size, **ItemType**        
**ItemAttributes Table : ItemID (PK), AttributeID, AttributeName, AttributeValue**    

This will allow you to enforce your constraint of 1:1 mapping easily and allow your to easily query all attributes using the AttributeName as long as those are standard.

This is probably the old-fashioned approach and not very object oriented but it might be more suitable based on future flexibility.