Thread: Partial foreign keys, check constraints and inheritance
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
On 11/17/05, Eric E <whalesuit@gmail.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 ;)
>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... I did think about that, but I disliked the idea of two fields of nulls for every one full field.... maybe it's not as bada way of doing it as I thought. EE Jaime Casanova wrote: >On 11/17/05, Eric E <whalesuit@gmail.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 ;) > > >
Eric E wrote: >> 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... > > I did think about that, but I disliked the idea of two fields of nulls > for every one full field.... maybe it's not as bad a way of doing it > as I thought. BTW, in most cases I have 5+ tables to do this, so that's 4+ fields of null in each row... EE > > > Jaime Casanova wrote: > >> On 11/17/05, Eric E <whalesuit@gmail.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 ;) >> >> >> > >
On Thu, 2005-11-17 at 13:36, Eric E wrote: > Eric E wrote: > > >> 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... > > > > I did think about that, but I disliked the idea of two fields of nulls > > for every one full field.... maybe it's not as bad a way of doing it > > as I thought. > > BTW, in most cases I have 5+ tables to do this, so that's 4+ fields of > null in each row... Could you use some kind of intermediate join table, so that it pointed to orders and then products / customers / othermidlevel tables pointed to it, and so did the problems table?
Scott Marlowe wrote: >On Thu, 2005-11-17 at 13:36, Eric E wrote: > > >>Eric E wrote: >> >> >> >>>>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... >>>> >>>> >>>I did think about that, but I disliked the idea of two fields of nulls >>>for every one full field.... maybe it's not as bad a way of doing it >>>as I thought. >>> >>> >>BTW, in most cases I have 5+ tables to do this, so that's 4+ fields of >> >> >null in each row...Could you use some kind of intermediate join table, so that it pointed >to orders and then products / customers / othermidlevel tables pointed >to it, and so did the problems table? > Clever - that intermediate table sounds like sort of a GUID for every element in the database, along with what table it belongs to, and the problems table points at that GUID. Sounds pretty promising. Thanks for the idea. EE
> On 11/17/05, Eric E <whalesuit@gmail.com> wrote: > > > > 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. Normally I just have multiple columns with all but one NULL. Alternatively you can make it a many-to-many relationship. So you have a problem table and then you have a problem_product, problem_customer, and a problem_order table. -- greg
On Thu, Nov 17, 2005 at 02:21:33PM -0500, Eric E wrote: > >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... > > > I did think about that, but I disliked the idea of two fields of nulls for > every one full field.... maybe it's not as bad a way of doing it as I > thought. What's wrong with multiple NULL fields? It's probably the cleanest, fastest way to do this... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461