Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Can you create just one entity named Sale, with the following attributes: Buyer, Seller, Price, and Quantity? –  AlexKuznetsov Nov 1 '13 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) –  Imray Nov 1 '13 at 9:55
add comment

2 Answers

up vote 2 down vote accepted

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
share|improve this answer
    
What is (PK)? –  Imray Nov 1 '13 at 9:56
    
PK = Primary Key. –  zdxai Nov 4 '13 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 Nov 4 '13 at 0:12
add comment

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.

share|improve this answer
    
So you're suggesting a boolean attribute in the PartSourcing table? –  Imray Nov 1 '13 at 9:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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