Thread: Foreign Key normalization question

I'm building an app that has a customers table, a locations table, a
products table, and a product_locations table.

They make a diamond shape.

The locations table and the products table each have a customer_id
column that links back to the customers table.

Then the product_locations table table has just two columns: a
location_id column and a product_id column, each linking back to the
appropriate table.

I want to write a constraint or a trigger or something else that makes
sure that before a (location_id, product_id) tuple is inserted into the
product_locations table, the system verifies that the product links to
the same customer as the location.

How do I do this?

Thanks in advance.

Matt

you can use setup a foreign key constraint in your create table so that column is only populated when
there is a value which syncs to the referenced value
http://www.postgresql.org/docs/7.4/interactive/sql-createtable.html

Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


> To: pgsql-general@postgresql.org
> From: matt@tplus1.com
> Subject: [GENERAL] Foreign Key normalization question
> Date: Tue, 2 Sep 2008 19:14:17 +0000
>
> I'm building an app that has a customers table, a locations table, a
> products table, and a product_locations table.
>
> They make a diamond shape.
>
> The locations table and the products table each have a customer_id
> column that links back to the customers table.
>
> Then the product_locations table table has just two columns: a
> location_id column and a product_id column, each linking back to the
> appropriate table.
>
> I want to write a constraint or a trigger or something else that makes
> sure that before a (location_id, product_id) tuple is inserted into the
> product_locations table, the system verifies that the product links to
> the same customer as the location.
>
> How do I do this?
>
> Thanks in advance.
>
> Matt
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Get thousands of games on your PC, your mobile phone, and the web with Windows®. Game with Windows

On Tue, Sep 2, 2008 at 1:14 PM, Matthew Wilson <matt@tplus1.com> wrote:
> I'm building an app that has a customers table, a locations table, a
> products table, and a product_locations table.
>
> They make a diamond shape.
>
> The locations table and the products table each have a customer_id
> column that links back to the customers table.
>
> Then the product_locations table table has just two columns: a
> location_id column and a product_id column, each linking back to the
> appropriate table.
>
> I want to write a constraint or a trigger or something else that makes
> sure that before a (location_id, product_id) tuple is inserted into the
> product_locations table, the system verifies that the product links to
> the same customer as the location.

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.

On Tue 02 Sep 2008 04:06:20 PM EDT, Martin Gainty wrote:
> you can use setup a foreign key constraint in your create table so that col=
> umn is only populated when
> there is a value which syncs to the referenced value
> http://www.postgresql.org/docs/7.4/interactive/sql-createtable.html

I don't think that will work.

When somebody inserts (99, 98) into product_location, I want to make
sure that product ID 99 has the same customer ID as location ID 98.

Matt

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

On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson <matt@tplus1.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

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@tplus1.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?

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

Matthew Wilson wrote:
> 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@tplus1.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?

You could add a trigger to your product_location table that just
double-checked the customers matched or prevents the insert/update.  A
PL/PGSQL function like this might help:

---------- 8< -------------------- 8< ----------

DECLARE
   is_ok BOOLEAN;
BEGIN
   SELECT p.customer_id = l.customer_id
   INTO is_ok
   FROM product p, location l
   WHERE p.product_id = NEW.product_id
   AND l.location_id = NEW.location_id;

   -- didnt find the product and location ... weird
   IF NOT FOUND THEN
       RETURN NULL;
   END;

   -- product customer matches the location customer
   IF is_ok = TRUE THEN
       RETURN NEW;
   END;

   -- product and location customers did NOT match, reject changes
   RETURN NULL;
END;
---------- 8< -------------------- 8< ----------

Disclaimer: I have no idea if that code works.  I just whipped it up now
without testing it.  That might do your checks without having to add
columns to tables you don't want to add.

Good luck.

-- Dante

On Tue 02 Sep 2008 05:35:25 PM EDT, D. Dante Lorenso wrote:
> You could add a trigger to your product_location table that just
> double-checked the customers matched or prevents the insert/update.  A
> PL/PGSQL function like this might help:
>
> ---------- 8< -------------------- 8< ----------
>
> DECLARE
>    is_ok BOOLEAN;
> BEGIN
>    SELECT p.customer_id = l.customer_id
>    INTO is_ok
>    FROM product p, location l
>    WHERE p.product_id = NEW.product_id
>    AND l.location_id = NEW.location_id;
>
>    -- didnt find the product and location ... weird
>    IF NOT FOUND THEN
>        RETURN NULL;
>    END;
>
>    -- product customer matches the location customer
>    IF is_ok = TRUE THEN
>        RETURN NEW;
>    END;
>
>    -- product and location customers did NOT match, reject changes
>    RETURN NULL;
> END;
> ---------- 8< -------------------- 8< ----------
>
> Disclaimer: I have no idea if that code works.  I just whipped it up now
> without testing it.  That might do your checks without having to add
> columns to tables you don't want to add.

Thanks!  This is what I was looking for.  Although I got a few syntax
errors in postgreSQL 8.3 until I changed a few END; statements to END
IF;

Also, I had to put:

    create or replace function check_customer ()
    returns trigger $$

at the top of this, and

$$ language 'plpgsql';

at the bottom.  I'm a novice at writing triggers, and this is really
useful.

Thanks again.

Matt