Re: Foreign Key normalization question - Mailing list pgsql-general

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@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Matthew Wilson
> Sent: Tuesday, September 02, 2008 3:35 PM
> To: pgsql-general@postgresql.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general