Hi all,
In my database application, I've repeatedly encountered a particular
issue, and I'm not sure I'm addressing it well, so I'd like suggestions
on how to deal with it. The problem is that I need something like a
partial foreign key - a foreign key where, based on field1, in some
rows
field1 references table A, and in some rows field1 references tableB.
Here's the gist of the design problem. Say I have a generic product
sales database: products, customers, orders - orders bring together
products and customers. Now I want a table to track problems
associated
with any of these items; products, customers or orders, and I want to
associated each problem with an item in one of the tables.
What's the best way to do this? My immediate reaction is that I want a
partial foreign key, but perhaps this is not a good way to go about
such
a design. I've also considered using inheritance. I could put all the
data fields for problems into a base table, then use separate inherited
tables for each of the tables I want to reference with foreign keys. I
avoided inherited tables in version 7.4 because they didn't seem
feature-complete. Finally, there's the option of doing what I do now,
which is use a check constraint.
Does anyone have ideas on the best way to acheive this behavior? Ideas
and advice would be much appreciated.
Cheers,
Eric
