Search Postgresql Archives

Re: Foreign Key normalization question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue 02 Sep 2008 04:40:55 PM EDT, Scott Marlowe wrote:
> On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson <matt@xxxxxxxxxx> wrote:
>> On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote:
>>> If the two subordinate tables ALWAYS have to point to the same place,
>>> why two tables?  Can't a customer have > 1 location?  I'm pretty sure
>>> IBM has more than one corporate office you could ship things to.
>>
>> Yeah, so the idea is one customer might have many locations and many
>> products.  And at each location, some subset of all their products is
>> available.
>
> You could have the product_locations have a custid1 and custid2 fields
> that reference the two parent tables, and then a check constraing on
> product_locations that custid1=custid2

You inspired me to change my tables to this:

create table location (
    id serial unique,
    name text,
    customer_id int references customer,
    primary key (id, customer_id)
);

create table product (
    id serial unique,
    name text,
    customer_id int references customer,
    primary key (id, customer_id)
);

create table product_location (
    product_id int references product (id),
    product_customer_id int references customer (id),
    location_id int references location (id),
    location_customer_id int references customer (id) check product_customer_id = location_customer_id,
    foreign key (product_id, product_customer_id) references product (id, customer_id),
    foreign key (location_id, location_customer_id) references location (id, customer_id),
);

This seems to work based on my informal testing, but it seems really
byzantine.  I wish I didn't have to explicitly put the customer IDs in
the table.

Is there a better way?



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux