Re: Foreign Key normalization question

Sounds like you really want this:

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

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

create table product (
id serial primary key,
name text,
location_id int references location (id) );

Jon

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Matthew Wilson
> Sent: Tuesday, September 02, 2008 3:35 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general