Can relationships have relationships?

Question

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.

Answer 1

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:

Parts: PartID (PK), PartName
Suppliers: SupplierID (PK), SupplierName
Pricing: (PartID,SupplierID) (Unique Index of these two Foreign Keys), Price
Purchases: PurchaseID (PK), (PartID,SupplierID) (Foreign Key), Quantity

Answer 2

I only see one relationship: Parts are sourced from Suppliers with a particular price and a flag indicating whether or not this is our preferred supplier for this part.

Table Parts: PartKey (PK), other atributes.

Table Suppliers: SupplierKey (PK), other attributes.

Table PartSourcing PartKey (PK, FK references Parts), SupplierKey (PK, FK references Suppliers), Price, IsPreferredSupplier.

I would be inclined to create a filtered unique index on the PartsSourcing table to enforce there only being one preferred supplier for each part.