Re: Partial foreign keys, check constraints and inheritance

On 11/17/05, Eric E <whalesuit(at)gmail(dot)com> wrote:
> 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
>

maybe you can solve it adding a new col and allow both to contain null values.

if these are not mutually exclusive you can avoid a check if they are
check that if one has a non-null value other has null...

> 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.

after you make pg_dump edit the file delete the check from the create
table and put it in an alter table add constraint at the end of the
file...

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)