Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cosmarchy
Starting Member
14 Posts |
 Posted - 2010-03-15 : 17:27:51
|
| Hello all,I am after some advise on the best way to achieve my goal. I am looking into how I would go about storing some related data in my database.I will have a table full of parts, which I need to link to other parts. Let me give an example. If I call up a sensor, it will need a lead to go with it but it could be a right angled lead or a straight lead; it must have one!!Obviously my parts table has all the info about the sensor, but what is the best way to add information about the leads? The trouble is that several parts could also require these leads... The thing is that it might require a choice of several parts in addition to the lead, such as a square reflector or a round reflector etc Can anyone offer some advise?Thanks very much. |
|
|
ray-SQL
Starting Member
18 Posts |
Posted - 2010-03-15 : 17:46:02
|
You should have another table (middle table) to store this relationship.tblPartsRelationshipspartID1partID2then you can store all the relationship you want. When you need to retrieve it, you will do something likeselect A.*, C.*from Parts AINNER JOIN tblPartsRelationships Bon A.PartID = B.PartID1INNER JOIN Parts Con C.PartID = B.PartID2 Ray Dai |
 |
|
|
cosmarchy
Starting Member
14 Posts |
Posted - 2010-03-17 : 16:03:33
|
quote:
Originally posted by ray-SQL
You should have another table (middle table) to store this relationship.tblPartsRelationshipspartID1partID2then you can store all the relationship you want. When you need to retrieve it, you will do something likeselect A.*, C.*from Parts AINNER JOIN tblPartsRelationships Bon A.PartID = B.PartID1INNER JOIN Parts Con C.PartID = B.PartID2 Ray Dai
Firstly thanks for your reply. Unfortunately I am not sure I understand...If I was to store my (parts) sensor in table A and link my associated (parts) leads in table B, what does table C do for me?Could you elaborate a little for me please?Thanks very much. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 16:13:50
|
All parts go in one table "Parts" - presumably people can buy a right-angled-lead on its own, if they want to? but if not have a flag for "Not for sale on its own".Then have an Accessories table. A sensor has accessories "Straight lead" and "Right angled lead". The column in this table is for a Part Number and that Part Number will JOIN to the main Parts table.If you want to enforce that a sensor can ONLY be purchases with a Lead (either the Right Angled Lead, or the Straight Lead) then you also need some sort of Bill-Of-Materials table, or a Rules table.A Rules table would say that a Sensor can only be bought with a Lead. "Lead" is a product group; the Accessories table then has to define that the associated products "Straight Lead" and "Right Angle Lead" are in the product group "Leads".I have never come across a system that enforces the purchase of the associated product as they have always been optional.But there is no point missing the possible sale of a Lead when someone buys a sensor  , so "recommending" a Lead is a good service, and even prevents a customer buying the Sensor and then being annoyed that they had no idea that they would need a "Lead" for it. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-17 : 16:15:06
|
There is only one table PARTS and one table tblPartsRelationships.All parts are stored in this table and there is no direct relation between parts.The table tblPartsRelationships is used for two different parts to get married.In the select example above the table PARTS is joined two times with two different aliases.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
cosmarchy
Starting Member
14 Posts |
Posted - 2010-03-18 : 16:51:07
|
| Excellent, thats clearer now Thanks for helping guys. |
|
|
|
|
|
|
|
|
Whats the best way to...