Normal Form-Second(Mr.Vinson?)

[FerryMary]

Mr.Vinson, Your advice along with that of others sent me on a refresher
course in the Normalization of a database. (regarding my post-plea for help-
"Is Data Just Data?", in this discussion group) I had totally warped the
concept of the Second Normal Form.

Eliminate Redundant Data--I interpreted that as meaning not to have
duplicate entries of the same information PER record. So I have a few tables
that are setup poorly. But from what I gather now it means not to duplicate
information relative to the key(not the specific record, but the whole field.
ie. vessel=boat not vessel=M/V Goodship Lollipop)

Please glance over the following and give me a yea or nay if "By God I think
She's Got It" appliesI'll just use two examples, hoping if I get these
right I'll get it right on the others.

Now:
tblfleet
vesselID
Name
Description (this is color scheme/school logos-still unique)
Class-(the fleet only has 5 classes of vessels)
Route-(Multiple boats are assigned to 1 route)
VehicleCapacity(Most same Based Class vessels have similar VehCap)
PassengerCapacity(same as VehCap)
(I have some fields like basic vessel power and radio call signs that should
not be there-because info covered under equipment, ie. horsepower and
certifications ie. FCC issues certificate with CallSign as identifier)
*The rest of the table fields are unique*

Should Be:
tblFleet
VesselID
VesselName
Description
Length
NetWeight
Beam
CellPhone(this item not covered anywhere else & unique)

tblFleetClass
VesselID
Class

tblFleetRoute
VesselID
Route

tblFleetCap
VesselID
VehicleCapacity
PassengerCapacity(I'm pretty certain where any vessel has X vehicle capacity
it has Y Passenger Capacity, if not this means 2 tables instead of just this
one "tblFleetCap")

Example2 of What I Have:
tblJob
JobNumber
VesselID(fk)
FacilityID(fk)
StatusID(relative to 5 different JobType)-which makes my JobDetails table
too big?
InitiatedDate
EndDate


tblJobDetails
JobNumber(fk)
JobDetailsNumber(pk)
SystemInvolved
EquipmentID
Fields I need for all 5 JobTypes(they do not apply to all jobtype)

tblStatus
StatusID
StatusName
JobType
JobTypeDescription

I Should Have:

tblJobType
JobTypeID
Description

tblStatus
StatusID
StatusName

tblStatusByJobType
JobTypeID
StatusID

tblJob
JobNumber
VesselID
FacilityID
InitiatedDate
StatusID (?)

tblJobDetails(JobType1)
JobNumber
JobType1ID
JobType1 Specific fields

tblJobDetails(JobType2
JobNumber
JobType2ID
JobType2 Specific fields ...and so on

Any input is greatly appreciated.
Mary

 

[Lynn Trapp]

Mary,
It looks a bit like what you are trying to do is create some sub-classing
tables, when what you MAY have wanted are child tables.

tblFleetClass
VesselID
Class

Will a vessel ALWAYS have a class? Will a vessel ALWAYS have only one class?
If the answer to both of those questions is 'yes', then I would leave the
class field in tblFleet and not have this table.

tblFleetRoute
VesselID
Route

Will a vessel always only have one route? Is VesselID the primary key of
this table? If it is, then you can only have one route per vessel. The same
situation might apply here as to tblFleetClass.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[FerryMary]

Lynn,

Thanks, I must still be interpreting some of what I'm reading incorrectly.
At this link

http://support.microsoft.com/?id=283878

I got the impression that I should be minimizing my tables even more than I
already am. Each table = 1 idea basically. So 1 idea does not necessarily
mean only 1 attribute. My fleet roster needs to incorporate all related
attributes relative to all vessels(they all have
length,breadth,beam,name,class,colorscheme,etc) & that change rarely if at
all..??..So Class is back in(it might change 1 once in 30 years) Route stays
out (we move boats around like checkers)

I use VesselID as key in Vessel/Route table as well as PKey in
Vessel/Equipment tables. I hope I have this right now.

TBMary

 

[Lynn Trapp]

You are very much on the right track, but I want to try to verify a few
things.

I got the impression that I should be minimizing my tables even more than
I
already am. Each table = 1 idea basically. So 1 idea does not
necessarily
mean only 1 attribute. My fleet roster needs to incorporate all related
attributes relative to all vessels(they all have
length,breadth,beam,name,class,colorscheme,etc) & that change rarely if at
all..??..

Yes, each table should store the date regarding a single type of entity --
i.e. vessels. Each field in each table should store a single, or atomic,
value for each attribute. This is not so much about whether or not a given
attribute will change, but whether or not it is a single attribute. When you
need multiple attributes that, essentially, describe the same thing -- i.e.
Route1, Route2, Route3,...RouteX, then you need a child table to store each
instance of a route that may be related to a vessel. Of course, if a boat is
only on one Route at any given time -- which seems likely to me -- then it's
only a matter of changing the Route attribute when needed and that wouldn't
require a different table, necessarily.

I use VesselID as key in Vessel/Route table as well as PKey in
Vessel/Equipment tables. I hope I have this right now.

If VesselID is the primary key in each of the sub tables, then you will only
be able to have one record per each record in the Vessels table. If a vessel
requires multiple equipment values, then your table might look like this.

tblVesselEquipment
VesselEquipmentID (PK)
VesselID (FK)
.....other fields related to a piece of equipment.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[FerryMary]

Thank you so much,

this eases my task considerably. You're right a vessel only works one route
at a time, (that's so funny) I think I'm so spreadsheet-minded, that
when I tried to force myself to stop thinking that way I overdid it.

Thanks again
Mary

 

[Lynn Trapp]

You're very welcome, Mary. It's so easy to bring the dreaded disease
"spreadsheet-itis" over from the Excel world. I think you are getting it
figured out.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html