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
/*
PLUG: http://plug.org, #utah on irc.freenode.net
Unsubscribe: http://plug.org/mailman/options/plug
Don't fear the penguin.
*/
