Re: Having an optional foreign key (ie. sometimes NULL) ?

> 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