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 bad a way of doing it as I thought.
EE
Jaime Casanova wrote:
Show quoted text
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
Show quoted text
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