Normalization of Tables

[JulietB]

Hi Everyone,
I imported some data into Access and I am having problems normalizing the columns. I have looked at the 1st, 2nd and 3rd normal form to model this table, and I am having problems with repeating groups.

Below I have pasted some sample data in the message area. Some details on the two tables 1 has Hrly Data(12am-11pm) and 1 has Dly Data(Day1-Day31). Each table has a 7 digit number which is the Station ID that the data was collected from. Each table also has an Element number, which describes the kind of data if it is (Wind,Sun,Solar,Precip,etc). Each table holds 20 or more different Station ID's and 4 different Element number. These numbers are repeated more than once as you can see from the sample data. I have concluded that this is a 1-M relationship as you can get more than one climate data from 1 Station. I have been trying to split the table but I don't know what to do with the Hly Data(12am-11pm) and the Dly Data(Day1-Day2).
HELP..........


Hourly Data
StationID Year Mth Day Element 12am 1am 11pm
1026270 1989 1 1 78 3.5 2.7 0.69
1026270 1989 1 2 78 0.30 1.0 4.19
1026270 1989 1 3 78 4.40 4.5 2.0
1026270 1989 1 4 78 1.79 1 1.60
1026270 1989 1 5 78 1.39 1.5 0.60
1026270 1989 1 6 78 1.0 0.69 -1.0
1026270 1989 1 7 78 -1.5 -1.3 1.10
1026270 1989 1 8 78 1.29 1.29 3.70
1026270 1989 1 9 78 3.59 3.40 1.29
1026270 1989 1 10 78 1.10 1.39 4.80
1026270 1989 1 11 78 4.30 3.79 4.59
1026270 1989 1 12 78 4.90 5.40 1.5
1026270 1989 1 13 78 1.10 0.80 -0.1
1026270 1989 1 14 78 0.69 -0.60 2.90
1026270 1989 1 15 78 2.59 3.00 4.5
1026270 1989 1 16 78 4.59 4.90 4.40
1026270 1989 1 17 78 3.79 4.09 6.40
1026270 1989 1 18 78 5.90 5.40 3.79
1026270 1989 1 19 78 4.40 5.30 6.80
1026270 1989 1 20 78 6.90 6.19 2.40
1026270 1989 1 21 78 2.40 2 0
1026270 1989 1 22 78 -0.10 0.20 0.80
1026270 1989 1 23 78 -1.5 -1.10 2
1026270 1989 1 24 78 1.29 1.20 2.79
1026270 1989 1 25 78 3 3.20 5.59
1026270 1989 1 26 78 5.59 5.69 4.19
1026270 1989 1 27 78 4.40 3.90 4.69
1026270 1989 1 28 78 4.69 5.0 5.40
1026270 1989 1 29 78 5.30 5.5 8.19
1026270 1989 1 30 78 9.30 9.80 -1.7
1026270 1989 1 31 78 -1.39 -2.2 -7.4


Daily Data
StationID Year Mth Element Day1 Day2 Day31
1026270 1989 1 1 4.30 4.400 2
1026270 1989 2 1 -6.40 -6.90
1026270 1989 3 1 3.79 3.59 7
1026270 1989 4 1 10.19 8.89
1026270 1989 5 1 22.39 19 20
1026270 1989 6 1 19.5 22.60
1026270 1989 7 1 15.89 15.80 19
1026270 1989 8 1 16.5 17.79 16
1026270 1989 9 1 17.39 19
1026270 1989 10 1 13.60 9.60 9
1026270 1989 11 1 8.39 7.69
1026270 1989 12 1 9.30 10.30 8

 

[elizabeth]

One thing that's confusing is you are using field names (12am, 1pm, etc.) as values, rather than descriptions.

This would work:
Station, Year, Month, Day, (Time in tblHourly), Element, Value.
Depending on your needs, you might want to convert the YMD fields to a date field.

The rule I like to use for normalization because it's so easy to remember is, "I swear to decompose until each field depends upon the key, the whole key, and nothing but the key, so help me Codd." That makes every data element in your table except the value part of a concatenated key. However, how far to normalize/decompose depends not just on your data but your purpose. To avoid a lengthy concatenated key, you could have a separate table for each Element Type, Year, etc, depending on your reporting needs. HTH.

 

[JimHorton]

JulietB,
A first thing, it looks like the 'Daily Data' table can be eliminated totally, it appears to be calculated values. Then in the Hourly data, one suggestion is to remove all the fields that pertain to '12am', etc. Have a single field, say 'Value', or whatever. and another field, 'TimeSlot'. Then you will, for each day, have 24 records, instead of 1 record with 24 extra fields. This will enable you to have a lookup table for the TimeSlots, and if you ever need to break down to 1/2 hr increments, it's very easy--you add records to the lookup table and no field changes are neede--now each day would have 48 records instead of 24.

You might at first balk about the number of records mulitplying, and it may seem like more space is used because the compound key must exist in every record, but this is more flexible. Crosstab queries can give tabular results (pivot with the TimeSlot as the Column Heading), and the derivative tables, such as Daily Data, can be Views instead of separate (and possibly unequal) tables.

You can also look to simplify the key by making it a single unique number, and having that relate to a table that says, ie. Key # 6546463 is Year 1999, Month 1, Day 1, Element 3, etc.

--Jim