Re: Simple data modelling problem.

Hi Lee,

You may have a one-to-many situation if a single product is ever always supplied by a single supplier or there may be a many-relationship between products and suppliers if a supplier supplies many products AND a single product may be supplied by 1 or more suppliers.

In the one-to-many relationship a single supplier table and a single product table will usually suffice with the product table containing a "foreign" key that is the "primary" key of the supplier that supplies that particular product.

In the many-to-many relationship there needs to be an additional"intersection" or "cross-reference" table to resolve the many-to-many relationship into two one-to-many relationships. Minimally, the cross-reference table could just hold the primary key of the supplier and the primary key of each product they supply. However, it's more likely that the cross-reference table will need to contain information that's unique or particular for that particular product/supplier. For instance, the supplier may have their own SKU or product ID different from other suppliers and perhaps different from you own company's needs. Each supplier may have their own pricing and discount structure which may need to be product specific or not, order lead times and minimum quantities etc. etc. The cross-reference table may (often) have it's own data for a particular product/supplier combination and may (often, in such a case) have relationships to other tables not inherent in either product or supplier.

Minimally you'll need two tables (product and supplier) in the one-to-many case. Or, three or even more tables if the relationship between product and supplier is a many-to-many relationship.

I'd recommend a good book on data modeling.

Rich Lyon

Lee Osborne wrote:
>
> Hi all,
>
> I'm new to this so bear with my stupidity! I have a list of suppliers whose
> table structure is no problem. However, each supplier needs to have a table
> of products that they supply. Surely I don't need to create a products table
> for each supplier? Help!
>
> How do I solve this problem elegantly?
>
> Many thanks,
>
> Lee.
Received on Sun Jul 22 2001 - 01:26:16 CEST