On 11/17/05, Eric E <whalesuit@xxxxxxxxx> 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 ;) ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
