Re: Foreign Key normalization question - Mailing list 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