Partial foreign keys, check constraints and inheritance
От | Eric E |
---|---|
Тема | Partial foreign keys, check constraints and inheritance |
Дата | |
Msg-id | 437CCA77.1030107@gmail.com обсуждение исходный текст |
Ответы |
Re: Partial foreign keys, check constraints and inheritance
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: