Re: Partial foreign keys, check constraints and inheritance

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 ;)
>>
>>
>>
>
>