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. The check constraint has the distinct
downside of making backups and restoration more complex, as it is added
during table creation, and not after data load.
Does anyone have ideas on the best way to acheive this behavior? Ideas
and advice would be much appreciated.
Cheers,
Eric
