db relation set up...

[Aerodyne]

Hi all,

Help would be appreciated please ;-)

Okay I have a client who wants to set up a web site to sell products …
The client does not hold any stock … everything is dispatched from the Distributor, here are what the tables look like:

--ProductsTbl--
*PrdtID
CatID
DistID

--CustTbl--
*CustID

--CategaryTbl--
*CatID

--DistTbl--
*DistID

--OrderTbl--
*OrderID
CustID

--OrderDetailsTbl--
*OrderDetailsID
OrderID
PrdtID
DistID

The relationships are:
CatTbl 8PrductsTbl (via CatID)
CustTbl 8OrdersTbl (via CustID)
OrderTbl 8OrderDetails (via OrderID)
PrdtTbl 8OrderDetails (via PrdtID)

How do I link the distributor … since they can sell many Products & a Product can have many Distributors. is it worth doing another table that hold this info but do not assign a key filed to it, in order to link the Distributor the the PrdtTbl & the OrderDetailsTbl?

TIA

 

[SQLSister]

You have a many to many relationship. Usually people handle this with a table (Called ProductsDistibutor inthe rest of the example) that has the id field from each of the two tables and nothing else. So it has a one to many relationship between Products and the ProductsDistributor table and a one to many relationship between the Distributo and ProductsDistributor table.

Questions about posting. See faq183-874

 

[Aerodyne]

So should it be somthing like:
--ProductsDistibutor--
DistID
PrdtID

and the relationship:
ProductsDistibutor 1-m DistributionTbl
ProductsDistibutor 1-m PrductsTbl

 

[SQLSister]

ProductsDistibutor m-1 DistributionTbl (Many products per distributor)
ProductsDistibutor m-1 PrductsTbl (Many distributors per product)

Questions about posting. See faq183-874