> 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