From: | Ryan Mahoney <ryan(at)paymentalliance(dot)net> |
---|---|
To: | Tom <tom(at)vms7(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Having an optional foreign key (ie. sometimes NULL) ? |
Date: | 2003-03-13 23:55:42 |
Message-ID: | 1047599741.1272.11.camel@dhcp-1004-38 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> Hello,
>
> I have the following tables:
>
> - company (eg. ABC Shipping Inc.)
> - product (eg. table, chair, pen, pencil)
> - client (eg. Joe's Insurance Company)
>
> and they relate to each other as follows:
>
> - a company has clients (company is a foreign key in client)
> - a company sells products (company is a foreign key in client)
>
> which is all well and good until I come to one specification in my design
> document which says that "some products will only be available to a single
> client whereas other products will be available to all clients".
>
> I thought the best way to get around this would be to have the client as a
> foreign key in products but for products available to all clients this won't
> work.
I think the cleanest implementation would be to create the mapping
table:
client_product_map (
client_id integer not null references clients,
product_id integer not null references products
);
CREATE UNIQUE INDEX client_product_map_uix ON client_product_map
(client_id, product_id);
This way you explicity map which products are available to which
clients. When you select products for a client, join using the mapping
table and you will effectively filter out what you don't need.
SELECT products.product_name FROM products, client_product_map WHERE
products.product_id = client_product_map.product_id and
client_product_map.client_id = 1;
YMMV
Good luck!
-r
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-03-14 00:25:45 | Re: ~*, case insensitiveness and national chars |
Previous Message | Manfred Koizar | 2003-03-13 23:36:15 | Re: Having an optional foreign key (ie. sometimes NULL) ? |