Thread: 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.

  Can someone advise how I can overcome this?

  Thanks, Tom.

On Thu, 13 Mar 2003 22:58:14 +0000, Tom <tom@vms7.com> wrote:
>"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.

It will work, if you let NULL represent "available to all clients":

CREATE TABLE client (id INT PRIMARY KEY, name TEXT);
INSERT INTO client VALUES (1, 'Joe''s Insurance Company');
INSERT INTO client VALUES (2, 'Second Client');

CREATE TABLE product (id INT, name TEXT, cid INT REFERENCES client);
INSERT INTO product VALUES (11, 'for Joe', 1);
INSERT INTO product VALUES (22, 'for all', NULL);

It's perfectly legal to have a nullable foreign key column.

Servus
 Manfred

> 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

> On Thu, 13 Mar 2003 22:58:14 +0000, Tom <tom@vms7.com> wrote:
>>"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.
>
> It will work, if you let NULL represent "available to all clients":
>
> CREATE TABLE client (id INT PRIMARY KEY, name TEXT);
> INSERT INTO client VALUES (1, 'Joe''s Insurance Company');
> INSERT INTO client VALUES (2, 'Second Client');
>
> CREATE TABLE product (id INT, name TEXT, cid INT REFERENCES client);
> INSERT INTO product VALUES (11, 'for Joe', 1);
> INSERT INTO product VALUES (22, 'for all', NULL);
>
> It's perfectly legal to have a nullable foreign key column.
>

Does "will only be available to a single client" refer always to the one
same particular client, or are there multiple clients that have unique,
exclusive access to different subsets of the product list?

~Berend Tober