CODEWITHSUNDEEP
mysql
Donnie P
Donnie P

Reputation: 11

MySQL, Is it possible to have multiple Foreign key values in one table looking at values from a differnt Table?

I have two Tables a PC_Build Table that consists of a PcID (primary Key), a FinalCost, and a PartID. I also have a Parts table that has PartID (Primary Key), Part_Name, Cost. Is it possible for my PC_Build to have multiple PartID's, that link to the Parts table??? Also is it possible for me to calculate the cost of the PC_Build, from the sum of the parts that are used in the build by getting the cost from parts, and adding it to the FinalCost variable in PC_Build.

Upvotes: 1

Views: 3195

Answers (2)

Rob Stevenson-Leggett
Rob Stevenson-Leggett

Reputation: 35679

Yes. It's perfectly allowed. You can have as many foreign keys to the same table as you like as long as the columns and foreign keys are named differently.

However, for the situation you describe, this isn't necessarily a good idea. It breaks normalisation and you will have problems if for example a PC has 4 of the same sticks of RAM in it and another has 8 or a PC has 3 CPUs but another has 1. How will you handle that situation? If you use foreign keys you would need to keep changing your table to accommodate all the different situations.

It would probably be better to have a PC_Build_Part table that had two columns, PcID and PartID. Then you could use joins to simply work out what parts belong to which PC.

Here's an example schema:

PC (PcID, BuildCost)

Part (PartID, PartName, PartCost)

PC_Part(PcID, PartID)

This is called a many-to-many relationship.

To calculate the BuildCost column you could do a query like the following:

SELECT SUM(p.PartCost) as TotalCost
FROM Part p
JOIN PC_Part pcp ON pcp.PartID = p.PartID
JOIN PC pc ON pc.PcID = pcp.PcID

This would allow you to flexibly handle many different PC configurations without having to change your schema.

Upvotes: 5

AndreKR
AndreKR

Reputation: 33668

If every part belongs to a certain PC, then you'll want to add a PcID column to the Parts table and remove the PartID from your PC_Build table. If you have a fixed catalog of parts and each PC can consist of multiple Parts and each part can be used in multiple PCs then you need an additional table (PC_to_Part) where you have a PcID and a PartID.

Yes, assuming the former method, you can find the cost of each PC using a query like

SELECT PcID, SUM(Cost)
FROM PC_Build
JOIN Parts ON (Parts.PcID = PC_Build.PcID)
GROUP BY PcID

Upvotes: 1

Related Questions