Re: Having an optional foreign key (ie. sometimes NULL) ?
От | Ryan Mahoney |
---|---|
Тема | Re: Having an optional foreign key (ie. sometimes NULL) ? |
Дата | |
Msg-id | 1047599741.1272.11.camel@dhcp-1004-38 обсуждение исходный текст |
Ответ на | Having an optional foreign key (ie. sometimes NULL) ? (Tom <tom@vms7.com>) |
Список | 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
В списке pgsql-general по дате отправления: