If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table design
In my DB i currently have a junction table: ModelsXREFSuppliers. It has FKs
to the Models table (Model_ID), SupplyParts table (SupplyPart_ID), and the Suppliers table (Supplier_ID). I have just created a new junction table called temporarily called Windows. This table contains the same 3 FKs, but it also has three additional attributes that apply for only the windows. Is good database design or should there only be one junction table? Now if there is only one junction table, about 90% of the entries will be empty for those three fields. I originally thought that having two tables was the best idea, and then copying the data if the record contained window data to the other table. Would the best approach be to have two separate junctions tables, one for windows and one for all the other parts? |
#2
|
|||
|
|||
Table design
A junction table is a junction table and that is all. All you should have in
it is the two foreign keys. If you have anything else, look at your design again. Dorian "JKarchner" wrote: In my DB i currently have a junction table: ModelsXREFSuppliers. It has FKs to the Models table (Model_ID), SupplyParts table (SupplyPart_ID), and the Suppliers table (Supplier_ID). I have just created a new junction table called temporarily called Windows. This table contains the same 3 FKs, but it also has three additional attributes that apply for only the windows. Is good database design or should there only be one junction table? Now if there is only one junction table, about 90% of the entries will be empty for those three fields. I originally thought that having two tables was the best idea, and then copying the data if the record contained window data to the other table. Would the best approach be to have two separate junctions tables, one for windows and one for all the other parts? |
#3
|
|||
|
|||
Table design
=?Utf-8?B?bXNjZXJ0aWZpZWQ=?= wrote in
: A junction table is a junction table and that is all. All you should have in it is the two foreign keys. If you have anything else, look at your design again. Bolox. A junction table is just a table. It'll have a key and various attributes as appropriate. For example: Students (*StudentID, HomeAddress, TermTimeAddress, CreditLimit) Courses (*CourseCode, FullName, Tutor, NumberOfStars) .... and the junction table is .... Registrations(*StudentID, *CourseCode, DateStarted, FeePaid, FinalGrade) Or what about Vehicles (*VIN, Make, Model, NumberOfSeats) Customers(*CustID, FullName, BillingAddress) .... and the junction table is .... Hiring(*VIN, *CustID, ConditionOnReturn, MethodOfPayment) and so on. Of course relationships have attributes. All the best Tim F |
#4
|
|||
|
|||
Table design
=?Utf-8?B?SkthcmNobmVy?= wrote in
news In my DB i currently have a junction table: ModelsXREFSuppliers. It has FKs to the Models table (Model_ID), SupplyParts table (SupplyPart_ID), and the Suppliers table (Supplier_ID). It is not clear from this what the relationship is between the three entities. What, for example, is a SupplierPart? The correct way to link three tables is usually in fourth or fifth NF vis: In 4NF, each Supplier suppliers various Models, but does so for every value of SupplierPart with which it's related. Similarly, each Model has a role in various SupplierParts but equally so for any value of Supplier which supplies her. Just as each SupplierPart gets its parts from various Suppliers but equally so for all Models that it has a role in. In this case, you need three entities to implement the three relationships. Think of People who have Skills and work on Projects: each Project needs various Skills. The three relations are PeopleHaveSkills, ProjectsNeedSkills, and PeopleAssignedToProjects. In 5NF, the three relationships are not independent. For example, Supplier A will supply Model X with SupplyPart1, and it will supply model B with SupplyPart 2, but will not supply A with 2 or B with 1. In the easier case, Bob will use his German on Project X and his Maths on Project Y. This requires a single table with three FKs to implement it. Note that in 5NF, you lose the information that Bob has German and Maths unless he is currently using them on a project. In that case you'll need all four tables, but the FKs will be different of course. If that is not clear it's because it's quite a tricky model to get right in your head. But you do need to get it straight or else the model will not work. Best of luck Tim F |
#5
|
|||
|
|||
Table design
Thank you tim, and you were right with the 5NF. In the automotive business,
suppliers change what they supply so often that it is barely worth keeping track of exactly what products they supply. that is why the DB creator decided to not keep track that way and stick with something similar to your example with Bob and his math and German. The major key is that a supplier can supply many parts to many models, and a part can be supplied by many suppliers and many models. I think that i might have two junction tables. One for the windows, it turns out that windows have between 8 and 10 additional attributes, and one for the rest of the products. Using this i can separate the data and have two subforms, one solely for window data and another for all the other parts. This seems to be the best way to go about it and thank you for all of your help. If you do have any suggestions feel free to make them. "Tim Ferguson" wrote: =?Utf-8?B?SkthcmNobmVy?= wrote in news In my DB i currently have a junction table: ModelsXREFSuppliers. It has FKs to the Models table (Model_ID), SupplyParts table (SupplyPart_ID), and the Suppliers table (Supplier_ID). It is not clear from this what the relationship is between the three entities. What, for example, is a SupplierPart? The correct way to link three tables is usually in fourth or fifth NF vis: In 4NF, each Supplier suppliers various Models, but does so for every value of SupplierPart with which it's related. Similarly, each Model has a role in various SupplierParts but equally so for any value of Supplier which supplies her. Just as each SupplierPart gets its parts from various Suppliers but equally so for all Models that it has a role in. In this case, you need three entities to implement the three relationships. Think of People who have Skills and work on Projects: each Project needs various Skills. The three relations are PeopleHaveSkills, ProjectsNeedSkills, and PeopleAssignedToProjects. In 5NF, the three relationships are not independent. For example, Supplier A will supply Model X with SupplyPart1, and it will supply model B with SupplyPart 2, but will not supply A with 2 or B with 1. In the easier case, Bob will use his German on Project X and his Maths on Project Y. This requires a single table with three FKs to implement it. Note that in 5NF, you lose the information that Bob has German and Maths unless he is currently using them on a project. In that case you'll need all four tables, but the FKs will be different of course. If that is not clear it's because it's quite a tricky model to get right in your head. But you do need to get it straight or else the model will not work. Best of luck Tim F |
#6
|
|||
|
|||
Table design
=?Utf-8?B?SkthcmNobmVy?= wrote in
: The major key is that a supplier can supply many parts to many models, and a part can be supplied by many suppliers and many models. First relationship models what can be fitted to what: Parts - CanBeFittedTo - Models Second relationship models where you get the parts from: Suppliers - CanSupply - Parts There may be also this one, for billing and invoicing: Suppliers - DidSupply - Parts Presumably you can fit any old B09119 Left Headlamp to any suitable model, regardless of who the supplier was? Hope that helps Tim F |
#7
|
|||
|
|||
Table design
thank you for your help tim, but somehow i got the feeling that we got a tad
of subject. my real question was kind of confusing in my original post, but i think i have done a much better clearing it up this time. Should i have a separate junction table for SupplyParts of category Windows, because they have additional attributes, or should i leave them in the ModelsXREFSuppliers table with all of the other SupplyParts and just add the additional fields to the table? i know that the second way will leave me with many many empty/null fields, and thats why i was leaning towards the first method. thank you for any help. also, our DB is not that complex, refering to the B09119 Left Headlamp. In ours it would be SupplierA supplies Headlamps to Model4 (just a small clarification). we just aim for the general part, not a specific type of the part. "Tim Ferguson" wrote: =?Utf-8?B?SkthcmNobmVy?= wrote in : The major key is that a supplier can supply many parts to many models, and a part can be supplied by many suppliers and many models. First relationship models what can be fitted to what: Parts - CanBeFittedTo - Models Second relationship models where you get the parts from: Suppliers - CanSupply - Parts There may be also this one, for billing and invoicing: Suppliers - DidSupply - Parts Presumably you can fit any old B09119 Left Headlamp to any suitable model, regardless of who the supplier was? Hope that helps Tim F |
#8
|
|||
|
|||
Table design
=?Utf-8?B?SkthcmNobmVy?= wrote in
: Should i have a separate junction table for SupplyParts of category Windows, because they have additional attributes, or should i leave them in the ModelsXREFSuppliers table with all of the other SupplyParts and just add the additional fields to the table? I really cannot answer this because I have no idea what a ModelsXREFSupplier is. A table models a real-world entity, whether that is a concrete thing like a Supplier or an abstract thing like Registration. I can see how you would have an entity like AbilityToSupply that would sit inbetween the Suppliers and Parts tables. That entity may well have other attributes like MaximumOnOneOrder or DaysToDeliver or OnlyUseAsALastResort -- I have no idea whether such attributes stop it being a "junction table" because that is a phrase I just don't use. I don't recognise that any type of table is any different from another type. It's just a table of records representing a real world thing. I also find it helpful to name the table after whatever it is that it is modelling: so something like CanSupply or even Catalogue would be what I would use in this situation. If you know what a real-life ModelsXREFSupplier is then fine: just make sure you document it for your successor who will have to debug and maintain the database. The short answer is that the "additional attributes" should be inside whichever table they are attributes of. If they are attributes of a ModelsXREFSupplier then put them in that table. If they are attributes of a supplier, that that is where they should be. It's actually a lot simpler than you are trying to make it, or so ISTM. also, our DB is not that complex, refering to the B09119 Left Headlamp. In ours it would be SupplierA supplies Headlamps to Model4 (just a small clarification). we just aim for the general part, not a specific type of the part. If SupplierA supplies Headlamps to Model4, but SupplierB supplies different Headlamps to Model5, then you have a different design -- that makes it like the second example I suggested above, or so I strongly suspect. Best of luck Tim F |
#9
|
|||
|
|||
Table design
The way your posts show up in my newsreader is that you have hijacked
an earlier thread by removing the " " on the subject line and deleting the earlier content. If that isn't what you did then there is something wrong in your posting or in my newsreader. The newsreader hasn't suffered any similar failures to date. If you do hijack a thread you aren't really getting your issue out to the wider world. Once people have finished a thread to their satisfaction they may never come back to it. However, they may well address a new thread with the same name. To start a new thread, follow the directions in your newsreader. HTH -- -Larry- -- "JKarchner" wrote in message news In my DB i currently have a junction table: ModelsXREFSuppliers. It has FKs to the Models table (Model_ID), SupplyParts table (SupplyPart_ID), and the Suppliers table (Supplier_ID). I have just created a new junction table called temporarily called Windows. This table contains the same 3 FKs, but it also has three additional attributes that apply for only the windows. Is good database design or should there only be one junction table? Now if there is only one junction table, about 90% of the entries will be empty for those three fields. I originally thought that having two tables was the best idea, and then copying the data if the record contained window data to the other table. Would the best approach be to have two separate junctions tables, one for windows and one for all the other parts? |
Thread Tools | |
Display Modes | |
|
|