Help designing small database.

Question

I have a small database that I need help designing. I have a VB.NET program that gets data from a .csv file and enters the data into an Access database. The data is from a "Runway Friction Test". A machine measures the level of friction on a runway due to rubber build up from airplanes landing and puts the data into a .csv file. I need help designing the tables for this data so that I can use the data as I need to.

After performing a Friction Test, I have one “Test” with two “Runs” (left side and right side of runway). This test data is recorded into two .csv files, one for each “Run”, and I extract the data I want to store. The data I need to store from each file is:

Runway Number, Runway Side, Test Date, Test Time, First Third Avg. Friction, Second Third Avg. Friction, Third Third Avg. Friction, Avg. Overall Friction.

After my “Test”, I have this example data to enter into database:

Run 1: “16R,34L”, “16R”, #12/2/09#, #8:51AM#, .75, .69, .79, .78

Run 2: “16R,34L”, “34L”, #12/2/09#, #8:51AM#, .70, .72, .76, .70

The decimal numbers represent a measurement of friction. The way I am entering it into the database is just as above but with an automatic PK.

What I want to be able to do is to make another table that combines the two runs into one record. Every record that has the same date will be inserted as one record (one test to each date) and the numbers of the two records becoming one would be averaged. I will then use this data to make charts of the friction levels over several months. I also want to be able to remove duplicates from the database in case someone enters a record more than once.

I am looking for some suggestions on how to design this simple data base. How many tables should I make? Should I normalize? Should I not use an auto PK? With such minimal data, I have thought of putting the entire test (both runs) onto one record. I have also considered normalizing to make more tables. The way I have it now I am having trouble doing what I want with the data (such as creating a table that averages the two "runs" numbers).

Answer 1

Sure, you could normalize. But the work involved to get the data in would be increased. I think you just need some useful queries to help you out, such as:

SELECT [Runway Number], [Test Date], [Test Time], 
     Avg([First Third Avg. Friction]) as First, 
     Avg([Second Third Avg. Friction]) as Second, 
     Avg([Third Third Avg. Friction]) as Third, 
     Avg([Avg. Overall Friction]) as Overall
FROM yourTable
GROUP BY [Runway Number], [Test Date], [Test Time]

Or:

SELECT [Runway Number],
     Avg([First Third Avg. Friction]) as First, 
     Avg([Second Third Avg. Friction]) as Second, 
     Avg([Third Third Avg. Friction]) as Third, 
     Avg([Avg. Overall Friction]) as Overall
FROM yourTable
GROUP BY [Runway Number]

OR:

SELECT [Runway Number], YEAR([Test Date]) as [Test Year],
     Avg([First Third Avg. Friction]) as First, 
     Avg([Second Third Avg. Friction]) as Second, 
     Avg([Third Third Avg. Friction]) as Third, 
     Avg([Avg. Overall Friction]) as Overall
FROM yourTable
GROUP BY [Runway Number], YEAR([Test Date])

I'm sure you get the idea...