Do I need a junction table?

Question

3.00/5 (2 votes)

See more:

SQL-Server-2005

I am trying to normalise my tables in a container tracking application, and have got a table called shipments that needs to keep track of:- the container seal, the vehicle registration and the trailer registration as well as arrivals and such.
Sometimes the shipment goes on a road train vehicle and requires two or even three seals.
In this case would this be the best way to go about setting up the tables?

tblShipments - ShipmentID - PK
ShipSealID - FK
TruckNo
TrailerNo

tblShipSeals - ShipSealID - PK
ShipmentID - FK
SealID - FK

tblSeals - SealID - PK
SealNo

Any help would be appreciated please, Database is Access 2007.

Posted 17-Mar-11 4:53am

sonsaeng

Add a Solution

Comments

Corporal Agarn 17-Mar-11 11:00am

If this is in Access 2007 then why the SQL2005 tag?

2 solutions

Solution 1

You should not have tblshipments and tblshipseals setup as displayed. In the shipments table you do not need the shipsealid us the shipseals table for that.

Posted 17-Mar-11 4:59am

Corporal Agarn

Comments

sonsaeng 19-Mar-11 11:54am

Thanks for taking the time to answer, and comments much appreciated

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Yusuf · Accepted Answer · 2011-03-17T05:07:00

Solution 2

When you modeling, look further than the real world it self. Your shipment may need a seal, but your seal does not need to know about the shipment, or does it?

tblShipment - ID - PK
              Description - Describes your shipment (You may or many not need this)
              TruckNo
              TrailerNo
              SealID - FK

tblSeal     - ID - PK
              Type - (Maybe there are different seals)
              Description - Describes your seal (may no be needed)

Mind you I have thrown some more fields as suggestion, not that you need them. You need to think about your model and see what makes sense for you and what does not.

Posted 17-Mar-11 5:07am

Yusuf

Comments

Corporal Agarn 17-Mar-11 11:50am

Thank you Yusuf. You said it much better.

Albin Abel 17-Mar-11 13:49pm

Correct. My 5

Espen Harlinn 17-Mar-11 19:40pm

5ed - again :)

sonsaeng 19-Mar-11 11:52am

Thanks for taking the the time to answer. The seal is required in the shipments table, but sometimes there can be 3 seals for each shipment, and one way to get round this is to add the first seal for queries, and then shock horror, add any more seal numbers in the comments/notes field. But this would defeat the object of the exercise, eg as when we get get scanned paperwork and the first seal record is unreadable we wouldn't be able to search on the second or even third seal, and of course the database wouldn't be normalised.

Yusuf 19-Mar-11 14:18pm

That would not be the right approch. If you need more than one seal for each shipment, then you create what is known as 'Mapping Table'. If you create a mapping table, then you don't need SealID in the tblShipement.

tblShipment - ID - PK
Description - Describes your shipment (You may or many not need this)
TruckNo
TrailerNo

tblSeal - ID - PK
Type - (Maybe there are different seals)
Description - Describes your seal (may no be needed)

tblShipmentSeals shipID - PK - points to your shipement ID
sealID - PK - points to your seal ID

You don't need any PK in the table, but if your table is getting large or your have queries that you will join the table with others, adding PK *may* have performance gain. Remember this will depend on your data modelling and specific scenarios, which can not be generalized in short answers.