Rookie after some advice

[JamesPatrick]

Hi all!

I can code queries well, but not confident with designing and normalization skills as yet. I wanted some advice on how you would go about putting together a db to house just this data. I'd like to see it in 3NF. The cranes move, so start date and end date probably need to be in a separate table. Not sure though. Can I get some thoughts? The data is comma delimited to use in Excel as CSV if you like. Thanks so much!

Crane Name,crane type,crane weight,crane weight capacity,Crane
manager,Current Country,Current Region,Current Site,Current Locale Start Dt,Current Locale End Dt

Crane 1,type1,500,10000,Crane Svcs Co 1,Country AAAABB,Region111111,XX-YY-Z-1,2/3/2007,1/13/2009

Crane 2,type1,400,5000,Crane Svcs Co 1,Country H7Y88U,Region999999JU,BB-UJ-44,10/5/2008,2/12/2009

Crane 3,type 2,400,4000,Crane Svcs Co 3,Country T55SEDS,Region8888HHH,JJ-NM-22-1,7/3/2008,2/15/2009

Crane 4,type 2,90,3500,Crane Svcs Co 2,Country LO998KL,Region8888HHH,Colonial 1,11/5/2008,3/18/2009

Crane 5,type 1,150,2000,Crane Svcs Co 5,Country AAAABB,Region111111,Mars XF,10/20/2008,4/1/2009

Crane 6,type 1,200,2500,Crane Svcs Co 6,Country T55SEDS,Region8888HHH,NULL,7/22/2008,9/9/2010

Crane 7,type 3,225,2750,Crane Svcs Co 4,Country LO998KL,Region8888HHH,NULL,2/2/2009,4/1/2009

Crane 8,type 4,500,10000,Crane Svcs Co 4,Country YYYYY,Region22222JJK,MP-NOM-GH,1/4/2009,3/12/2009

Crane 9,type 2,150,2000,Crane Svcs Co 2,Country IKI556B,Region999999JU,Plant FGH,6/1/2006,8/28/2009

Crane 10,type 1,820,14000,Crane Svcs Co 2,Country HHNH334,Region00000MM,Upstance 45,12/20/2008,NULL

Crane 11,type 5,600,13500,Crane Svcs Co 2,Country T55SEDS,Region8888HHH,Wireland field,12/21/2008,3/25/2009

Crane 12,type 1,1000,16000,Crane Svcs Co 3,Country IKI556B,Region999999JU,Crates JJK,10/25/2008,11/20/2009

Crane 13,type 2,400,4000,Crane Svcs Co 2,Country HHNH334,Region00000MM,GH-JO-MM-K,8/25/2008,4/25/2009

Crane 14,type 1,350,3500,Crane Svcs Co 5,Country HHNH334,Region00000MM,NN-NM-TY,6/1/2008,NULL

Crane 15,type 2,500,11000,Crane Svcs Co 6,Country LO998KL,Region8888HHH,Planes High,6/1/2008,8/1/2010

Crane 16,type 1,150,2000,Crane Svcs Co 5,Country YYYYY,Region22222JJK,UT-IT-GT,4/1/2007,2/15/2010

Crane 17,type 1,800,14000,Crane Svcs Co 2,Country IKI556B,Region999999JU,Mulyard Low,2/3/2007,4/25/2009

Crane 18,type 3,150,2500,Crane Svcs Co 2,Country LO998KL,Region8888HHH,NULL,8/23/2007,6/1/2009

Crane 19,type 2,600,12000,Crane Svcs Co 3,Country T55SEDS,Region8888HHH,GT-UOL-BHM,11/1/2008,7/25/2009

Crane 20,type 3,500,10000,Crane Svcs Co 6,CountryT55SEDS,Region8888HHH,Gore Falls,11/1/2008,4/25/2009

[JamesPatrick]

As this is for my development, I'm curious about difference from 1NF to 2NF and then to 3NF. thnx!!

[Ramy Mahrous]

You didn't normalize you DB design, you need to have table for Crane, Manager, Country, Region and Site.

[pclfw]

I can code queries well, but not confident with designing and normalization skills as yet. I wanted some advice on how you would go about putting together a db to house just this data. I'd like to see it in 3NF. The cranes move, so start date and end date probably need to be in a separate table. Not sure though. Can I get some thoughts? The data is comma delimited to use in Excel as CSV if you like. Thanks so much!

Crane Name,crane type,crane weight,crane weight capacity,Crane
manager,Current Country,Current Region,Current Site,Current Locale Start Dt,Current Locale End Dt

1) First Normal Form - http://en.wikipedia.org/wiki/First_normal_form
To get to FNF we have to remove the repeating fields. Current Country,Current Region,Current Site,Current Locale Start Dt,Current Locale End Dt have to come out because "the cranes can move." This will allow you to have a one to many releationship CRANE to LOCATION

Crane Manager can exist either in the main CRANE table or in a MANAGER table. Will a crane ONLY EVER have one manager? If yes then keep it in the CRANE table, if NO/MAYBE then move it to a MANGER table with some extra data items such as a link to the EMPLYEES table and ManagerStartdt, ManagerEndDt.

Now you have the following tables -
CRANE
RowId,
Crane Name,
Crane Type,
Crane Weight,
Crane Weight Capacity

CRANE_LOCATION
RowId,
CraneId,
CurrentCountry,
CurrentRegion,
CurrentSite,
CurrentLocaleStart Dt,
CurrentLocaleEnd Dt

CRANE_MANAGER
RowId,
CraneId,
EmplyeeId,
ManagerName,
ManagerStartDt,
ManagerEndDt

2) Second Normal Form - http://en.wikipedia.org/wiki/Second_normal_form.
Looking at each table we can see that the CRANE data items are all directly dependent upon the CraneName.
The LOCATION table will need to change though.
The CurrentRegion is dependent upon the CurrentCountry, and the
CurrentSite is dependent upon the CurrentRegion

So the tables now look like this -

CRANE
*RowId,
Crane Name,
Crane Type,
Crane Weight,
Crane Weight Capacity

CRANE_LOCATION
*RowId,
*CraneId,
OnSiteId

ONSITE
*RowId
*SiteId
LocaleStart Dt,
LocaleEnd Dt

CRANE_MANAGER
*RowId,
*CraneId,
EmplyeeId,
ManagerName,
ManagerStartDt,
ManagerEndDt

COUNTRIES
*RowId,
Country,

REGIONS
*RowId,
*CountryId,
Region,

SITES
*RowId,
*RegionId,
Site
(* = Primary key columns)

3) Third Normal Form - http://en.wikipedia.org/wiki/Third_normal_form

This one is easy for this example. We can see each data item is directly dependent upon the items in its key.

So the design is now more flexible and can actually hold more information. The ONSITE table can hold the location history for a crane. The Manager table lets managers manage many cranes and also easily change the cranes they do manage. We can also get a history of the cranes managed by a manager or the managers who looked after a crane.

This is now in a fairly crude 3NF. We now have to OPTIMIZE it. For instance the CONTRY, REGION and SITE tables could be roled up into a LOCATION table. This will save two disk accesses whenever the location is required.

Your turn now. Look long and hard at you data and the information that you want to get from it.

PCLFW