Database Design Theory?
shane at hathawaymix.org
Tue Nov 8 20:02:55 MST 2005
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.
More information about the PLUG