Normalization Question

[Melinda]

I am trying to design a database that tracks fleet
vehicles, their related costs, and the ending monthly
odometer readings for each vehicle. The table I am
struggling with is the odometer readings. According to
the data I have learned/read, date sensitive data should
be stored in one field. Below is the layout of the table
as I have it now. Would someone please suggest to me the
best way to handle this? I need for my users to be able
to enter the odometer readings each month, easily and be
able to calculate miles/mo.

tblOdometerReadings
strVehicleID (pk)
strYear
strJan
strFeb
..
..
..
strDec

Thanks.

Melinda

 

[Jim Shaw]

What you are dealing with is a classic log file or journal. These are
implemented as an associative table with a compound primary key consisting
of two foreign keys: VehicleID and Date. The Date is considered to be an
entity, but its table is not normally implemented unless you are using a
manufacturing shop colander. Thus, your design would be:

tblOdometerReadings
strVehicleID (pk)
dteMonthEnding Date(pk)
intOdometerReading
....

This has the added advantage of forcing only one odometer reading per date
if you don't allow duplicate compound keys. You do need to allow duplicate
dates though so other vehicle data can be reported.
Hope this helps

Jim Shaw

 

[Graham Mandeno]

Hi Melinda

Three fields only:
VehicleID
ReadingDate
Reading

Having one column for each month definitely violates the requirements of
normalization.

 

[Marshall Barton]

I am trying to design a database that tracks fleet
vehicles, their related costs, and the ending monthly
odometer readings for each vehicle. The table I am
struggling with is the odometer readings. According to
the data I have learned/read, date sensitive data should
be stored in one field. Below is the layout of the table
as I have it now. Would someone please suggest to me the
best way to handle this? I need for my users to be able
to enter the odometer readings each month, easily and be
able to calculate miles/mo.

tblOdometerReadings
strVehicleID (pk)
strYear
strJan
strFeb
.
.
.
strDec

The table should have these fields:
VehicleID Long (foreign key to vehicle table)
DateRead DateTime
Reading Long

The Primary Key index can consist of the two fieds VehicleID
and DateRead. You can use the DateRead text box on your
data entry form to enforce the day of the month. For
example the users can enter the date as 6/2004 or June 2004
and Access will make it the first day of the month. If you
think they might enter some other day of the month (e.g.
6/7/04) you can use the text box's AfterUpdate event to fix
it:
Me.txtDateRead = DateSerial(Year(Me.txtDateRead), _
Month(Me.txtDateRead), 1)