Master/detail table setup - suggestions please!

I'm sure this is a common scenario, but have not seen a comprehensive example anywhere, so would appreciate some of your suggestions...

Let's say we're creating a *purchase* pricelist for a Product (required), Supplier (optional), Method of Shipment (optional) and Effective Date (required). Tables PRODUCT, SUPPLIER and MOS exist, with primary keys.

Then there is a *sales* pricelist showing, for each purchase price record, the markup to one or more retailers. Table RETAILER exists with a primary key.

I have two main problems:
1. I wish to interpret NULL supplier or method of shipment as "any other supplier" or "any other method of shipment". How do I constrain the purchase pricelist to include max of one such entry for each Product/Date combination?

2. Does the sales pricelist have to include product, supplier, method of shipment and date columns again as foreign keys, or is there a way (and is it more sensible) to create some unique "Purchase pricelist ID" through which the two tables can be linked?

In particular, supplier and method of shipment entries are provided from an external source, and I'm not keen to "manually" insert an "any other..." entry in either SUPPLIER or MOS tables.

Thanks in advance for any help/suggestions/examples you can provide.

Paul Bird.

--
P R Bird, Analyst/Programmer
SmartPage Technology Ltd.
smartpage_at_cwcom.net Received on Fri Apr 23 1999 - 08:49:05 CDT