Re: Foreign Key normalization question

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.

And I need to track many customers.  So, one customer sells fortified
wine (a product) at one location and fancy champagne at another
location.

Meanwhile, a different customer sells lottery tickets at a different
location (location number three) and sells handguns at a fourth
location.

So, I'd have tuples in product_location that look like this:

(ID of location #1 belonging to customer #1, ID for fortified wine),
(ID of location #2 belonging to customer #1, ID for fancy champagne),
(ID of location #3 belonging to customer #2, ID for lottery tickets),
(ID of location #3 belonging to customer #2, ID for handguns),

I want to guarantee that products and locations don't get mixed up
regarding customers.  In other words, since, customer #1 only sells wine
and champagne, I want to prevent somebody from putting into
product_location a tuple like this:

(ID of location #1, ID for handguns).

Here's all my tables:

create table customer (
    id serial primary key,
    name text
);

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

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

create table product_location (
    product_id int references product (id),
    location_id int references location (id),
);

I want to make sure that when somebody inserts a (product_id,
location_id) tuple into product_location, the product_id refers to a
product that has a customer_id that matches customer_id referred to by
the location_id's location.

Matt