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@xxxxxxxxxx> 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
