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. ERD Relationship to Relationship

  • Can you create just one entity named Sale, with the following attributes: Buyer, Seller, Price, and Quantity?
    – A-K
    Commented Nov 1, 2013 at 1:25
  • @AlexKuznetsov No because the supplier we purchase the part from can be the ONLY buyer who we purchase that part from. However our database also needs to allow us to keep track who sells what and at what price. This calls for a many to many relationship. (fyi the "Buyer" is us, so an attribute "Buyer" is unnecessary) Commented Nov 1, 2013 at 9:55

2 Answers 2


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
  • What is (PK)? Commented Nov 1, 2013 at 9:56
  • PK = Primary Key.
    – zdxai
    Commented Nov 4, 2013 at 0:07
  • BTW, I don't think it's worth having a boolean column for preferred supplier. Your Purchases table will already include suppliers which you bought a part from - these are essentially your "preferred" suppliers in this table anyway. So I don't think you need it or the complexity that comes with it. HOWEVER This is assuming that there won't be an off chance that you will buy the same part from another supplier - this will then break the above rules in this comment suggestion.
    – zdxai
    Commented Nov 4, 2013 at 0:12

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.

  • So you're suggesting a boolean attribute in the PartSourcing table? Commented Nov 1, 2013 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.