Re: Foreign Key normalization question

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(at)tplus1(dot)com> 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?