I have the following requirements (as part of a larger system).
In the database, I need to maintain a list of parts
, suppliers
who sell those parts.
Many suppliers
can sell the same part
and each part
can be sold by many suppliers. However, the prices vary.
In my system, I should be able to keep track of the one, and only one, supplier
we purchase a specific part
from.
The way I figure it, I need two relationships on the entities Supplier
and Part
. One relationship Sells
recording that the particular supplier
carries the particular part
and vice versa, and another relationship Purchase
that records who we purchase an part
from.
However, I don't record the attribute price
, twice. I think that would be bad practice. Therefore, I ask, can I relate one relationship to another?
I've included a sample diagram below. The dashed line is what I'm not sure about.
Best Answer
You want to avoid any inconsistency in prices - and you have already identified this in your question.
I'd say use a "Pricing" table which stores Supplier-Parts(PK) and Prices(Float or Decimal value etc).
That way, each part from a different supplier has a different price. In other words, the price depends on what the Part is AND who the Supplier is (Supplier-Part is the Primary Key or Unique Index).
The Purchases table should just include records from this new "Pricing" table - which links to all the info from Parts, Suppliers and Prices.
Makes sense?
EDIT: I just noticed. You are relating Parts and Suppliers again in your Purchases table, when you already do it in your Sells table.
Just have these 4 tables: