Reducing Repetition on Location

[flebber]

I am doing a sports database and would like some advice on table structure. Using access 2007.

I am trying to stop the repetition of "location" in tables. I already have created a location table with LocationID, State and Location Name. What I want to record is that each location will have events at varying distances.

What my aim is, is to record best time, average time by distance at location. So that later I can reference which locations have which distances and if wanted best times per distance or per location.

My thought is that I would need to create a table for each distance possible and then list the locationName and LocationID of all locations that feature this distance and then the respective best time and average time next to each location.

Is there a better technique?

 

[dportas]

My thought is that I would need to create a table for each distance possible and then list the locationName and LocationID of all locations that feature this distance and then the respective best time and average time next to each location.

One table per distance? Why not just one table and have distance as an attribute in that table?

If LocationID determines LocationName then I think you should not have both in that table. Are you familiar with what normalization means and what a non-key dependency is?

 

[flebber]

One table per distance? Why not just one table and have distance as an attribute in that table?

If LocationID determines LocationName then I think you should not have both in that table. Are you familiar with what normalization means and what a non-key dependency is?

I will use lookup wizard to lookup locations table to reference locations. Thats normalized isn't it, separating each characteristic isn't it? Separating each element until its smallest element, then referencing to reduce redundancy. I am learning and I see I may have been unclear in my first post last night.

I am having trouble getting the distance, time and location layout correct though. Since each location has mulitple distances and each distance has different times based on location. Either way its seems to lead me to repeat data. i.e. say for four locations there are six distances and each time is unique but could change.

Or in this case is repetition unavoidable.