Is this poor design? Should I change it? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

Other Forums

MS Access

Is this poor design? Should I change it?

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-08-28 : 11:16:48

I have 3 tables for shipping information:


Table1: (tblLoadInformation)
Date | ProjectID | LoadNumber | Carrier | Trailer | BL#

Table2: (tblLoadConfiguration)
Date | ProjectID | LoadNumber | Component | Stack | Level | Position

Table3: (tblLoadNotes)
Date | ProjectID | LoadNumber | Note | IsGeneralNote

As you can see, in all 2 tables there is a Date, ProjectID, and LoadNumber. These values together are what I use for joins etc...

What I was thinking of doing was changing this to use a AutoNumber LoadID. I will be converting this to SQL Server one day so I do not want to screw myself up. Any guidance would be helpful.

New design:


Table1: (tblLoadInformation)
LoadID | Date | ProjectID | LoadNumber | Carrier | Trailer | BL#

Table2: (tblLoadConfiguration)
LoadID | Component | Stack | Level | Position

Table3: (tblLoadNotes)
LoadID | Note | IsGeneralNote

LoadID would be the AutoNumber field. Any advice?

Mike B

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-08-28 : 12:27:24

Can the information in these tables not be all put into one table ?

Does a carrier have mutiple configurations or is it based on a single load ?

If a carrier HAS multiple configurations would it be better linked to the load information table ?

My 2 pence anyway. With the info i can see it seems this data could be rolled into one table.

Paul

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-08-28 : 12:50:55

quote:

With the info i can see it seems this data could be rolled into one table.

First let me explain what it all is.
Date - is a shipment date
ProjecID - is an identification code for the project
LoadNumber - is the load number shipped to the project on that date
Carrier - is the trucking company who took the load
Trailer - is the trailer the components were loaded on
BL# - is the bill of lading number used by MTO
Component - is one of many components that can be loaded on a load
Stack, Level, and Position is where the component is on the load.

There is a many to one relationship between the components and loads.

There is a many to one relationship between the loads and the project.

There is a many to one relationship between notes and loads.

Does this clear it up at all?

I don't think you can have it all on one table without duplicating all the data.

Mike B

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-28 : 12:52:55

I suspect you have many tblLoadConfiguration and tblLoadNotes per tblLoadInformation?

Should there be any connection between tblLoadConfiguration and tblLoadNotes other than the ID?

If there is only on tblLoadConfiguration per ID then why is it separate? maybe it is entered at a different time or under control of a different department - both valid reasons for keeping on a separate table.

Other than that rolling Date ProjectID LoadNumber into a single ID is a justifiable thing to do - whether it is a good idea or not is another question. You forcing joins to the tblLoadInformation table to get this info - basically making this a central table. But you are also making the join fields smaller and so more efficient.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-08-28 : 13:47:21

Thanks for your reply nr. I have one question for you and hopefully a
better explanation of the database:


Explanation first:

ShippinDate (1)--->(+1) Project (1)--->(+1) Load (1)--->(+1) Notes
                                             |__(1)--->(+1) Components
                                             |__(1)--->(1) Carrier

As you can see, there are many projects that can be shipped on one
day, each project having many loads, each load having many notes and
many components, no relationship between components and notes other
then load number, and each load having one carrier.

Question regarding my "rolling" the Date, ProjectID, and LoadNumber
into a LoadID. In your honest oppinion, is this neccessary?

Mike B

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-28 : 14:27:51

Mike,

Your table and key structure look fine. The only apparent benefit you'll get from changing to a LoadID field is a matter of convenience, and possibly some efficiency, but not necessarily. There was an interesting discussion on this topic of surrogate keys here: [url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=18676[/url]

As you can see, there is a strong argument that to change to a surrogate key would indeed be poor design, rather than the other way around as you have asked. But depending on your situation, you may find it handy to change. It's really one of the "great debates" in the database world, and will never really be resolved on one side or the other to everyone's satisfaction.

Edited by - ajarnmark on 08/28/2002 14:29:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources