Table design

[JKarchner]

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?

 

[mscertified]

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

 

[Tim Ferguson]

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

 

[Tim Ferguson]

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

 

[JKarchner]

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]

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

 

[JKarchner]

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]

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

 

[Larry Daugherty]

The way your posts show up in my newsreader is that you have hijacked
an earlier thread by removing the "Re: " 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