Database Design Theory?

[Shane Hathaway]

Ross Werner wrote:
> What I typically do is have a "fruit" table, an "orange" table, and a 
> "banana" table with the primary key of the "orange" table also being a 
> foreign key to the "fruit" table, and ditto with "banana". Unfortunately 
> this doesn't technically prevent (from the database standpoint) a 
> particular fruit instance from being both an orange *and* a banana, and 
> it's a headache when trying to figure out the types and information of a 
> list of fruits.
> 
> Anyone have a better way?

You could add an after-insert trigger to the "fruit" table that aborts 
the transaction unless exactly one sub-table has a corresponding row. 
The trigger would have to fire at the end of the transaction.

However, if I were designing this, I would reason that the schema as it 
stands doesn't capture the intent that a fruit must have exactly one 
subtype.  I would probably add a column to the "fruit" table that 
disambiguates this.  It might be an enumeration or a string.

Shane