November 16, 2019 at 6:57 am
An exercise I was reading is about a bicycle shop. There is a table called "Cycle" and a table called "Parts." The question is asking, "what is the best foreign key for this relationship?"
To answer this question, first I have to determine how these tables should be setup. Since a cycle will have many unique parts, what is the best way to setup this relationship? I have two pictures attached with the primary key table (at the top) pointing to the foreign key table (at the bottom), with the foreign key in red. Between these two pictures, what is the proper way to setup these tables?
I understand the idea behind these relationships but on this one I'm just not seeing it. I think the solution they provided might be wrong.
November 16, 2019 at 3:22 pm
I imagine for this you'd need a third table, a junction table, since a cycle has many parts and each part can be used in many cycles.
John
November 16, 2019 at 4:55 pm
This example seems over-simplified, to the point of being impractical.
To implement this in the real world, I'd be looking to develop a standard BOM model ... see here, for example.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 17, 2019 at 4:41 pm
I think you need a new CycleParts table with two columns CycleId and PartId. You need 2 foreign keys on this table. One pointing to the Cycle table the other to the Parts table. Your primary keys should be on CycleId on the Cycle table and PartId on the Parts table. The CycleParts table could have a compound primary key on columns (CycleId, PartId).
November 18, 2019 at 12:59 am
I think you need a new CycleParts table with two columns CycleId and PartId. You need 2 foreign keys on this table. One pointing to the Cycle table the other to the Parts table. Your primary keys should be on CycleId on the Cycle table and PartId on the Parts table. The CycleParts table could have a compound primary key on columns (CycleId, PartId).
Thank you for your reply. What you are saying makes sense. Please see the new attached pic I created. Is this what you were referring to?
November 18, 2019 at 1:11 am
November 18, 2019 at 1:57 am
<li style="list-style-type: none;">
- Yes
<li style="list-style-type: none;">
- Yes, and a lot more. On some Fact tables in data warehouses it's not uncommon to have over 100 foreign keys on a table. A foreign key must point to a primary key or a unique index on another table.
<li style="list-style-type: none;">
- Really it does little more than enforce uniqueness, so prevent duplicates. If you do not want to ensure uniqueness on those two columns, maybe the same part could be installed on different dates or two of the same parts are needed on a cycle, you could ensure uniqueness with a different way. Maybe by creating a unique constraint/index or primary key on CycleId, PartId and received date. But this depends on the specifications of the system.
Regarding question #3, let's say I didn't have columns called "Installed" and "Date Received." I just put those in there to give examples of other fields. So I I didn't have those fields, what would the answer to question 3 be?
November 18, 2019 at 2:08 am
Regarding question #3, let's say I didn't have columns called "Installed" and "Date Received." I just put those in there to give examples of other fields. So I I didn't have those fields, what would the answer to question 3 be?
It's not uncommon to resolve a many to many relationship by adding a table with just two columns. Also, you really need to enforce uniqueness on this table. To do this you can make a compound primary key on the two columns or just add a unique constraint or index on them. It's also a good idea for every table to have a clustered index. The default clustering of a primary key is clustered.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply