Triathlon and running database

[timbits35]

Hi,

I have built a database for my triathlon and running race results. My goal is to enter my swim, bike and run times and then calculate based on the distance of each, the time per 25m for the swim, the km/hr for the bike and the km/hr and min/km for the run. And if I just do a run, the km/hr and min/km. Everything seemed to be ok until I wanted to calculate my km/hr and min/km. I had asked for help in the queries section and was given a formula which worked well until I realized that not all triathlons are the same distance and not all events are the same distance either. For example a sprint triathlon is usually 750m swim, 20km bike and 5km run, but I have done some which have a 500m swim or a 27km bike etc and my formulas don't work because I put fixed numbers. And the comment in the queries from was that maybe my tables are not normalized properly.

Here are my tables :

tblage : for the age categories eg. 40-44, 45-49 etc
tbltype : for the type of event eg. sprint triathlon, olympic triathlon, 5km run etc.
tbldistance : distance of the event eg. 750m-20k-5k, 1k-27k-8k etc.
tblevent : the name of the event eg. Esprit Triathlon de Montréal, Triathlon Ste-Agathe etc.
tblrace : the main table that contains the data like the date, bib #, weather, comments and all the race times.

Note that an event may have many types and distances, so I could do a sprint triathlon at Esprit Triathlon de Montréal, but the next year I could do an Olympic triathlon at the Esprit Triathlon de Montréal. Also as mentioned not all sprint triathlons are the same distance.
But I can only do one race at a time, so when I am entering a record in tbl race, I can only choose 1 type of race, 1 distance and 1 event.
For the race times, they are all number fields in order to allow me to do calculations and proper sorting.

Can you please take a look at my tables and see if they make sense.

I am wondering if in the tbldistance I should add numeric fields because the field 750m-20km-5km is a text field.

Thank you

If you need to see my database, I can send it, it is in Access 2003

 

[plog]

Nope not normalized. First, you're not getting anything out of your 4 lookup tables. A table with just one real field (ID's don't count) aren't necessary. Take type for instance, instead of storing it as text in tblrace you are storing it as a number and then looking up that text--you've not really accomplished anything in terms of a relational database.

You've done a good job identifying your entities (events, distances, etc.), but I don't think you have all the attributes (fields) allocated correctly.

Looking at the big picture, I believe you have 3 "levels", each with a 1-many relationship with the one beneath it:

Event - tblEvent - i.e. Hawaii Iron Man, Boston Marathon, Alcatraz Triatholon

Race - tblrace, but with no times nor distances - has date, EventID, your age group, bib.

Competition - new table - this is where the times and distances are kept. If its a triatholon it has 3 records, if just a run, it has one. Links back to tblRace via RaceID

The only thing that doesn't make sense is your types. You explain that there are sprint triatholons, but then you are unable to definitively say what that is comprised of (sometimes swim this, sometimes swim that, somtimes run this, etc.). I don't think the type gives you any information, especially since there's no real definition.

I would try and get your data into those 3 tables I described. Its really just a matter of making that competition table and moving the distance/time fields from tblrace into it. Give it a shot and post back what you have.

 

[timbits35]

Hi,

Thank you for your reply. I don't really understand your comment about the lookup tables. If there is no lookup table for example for the age group, this would imply that this would be stored as a lookup field in a table. There has been great discussion in these forums as to the fact that this is something not to be done. If I have a lookup field it also makes it more difficult to add new age categories or types etc.

I also don't understand your definition of events. Take for example the Esprit triathlon. This is an event that has many types of races : an Ironman, half Ironman, Olympic triathlon, sprint triathlon. I want to be able to say for example that on September 6, 2014 at the Esprit triathlon, I did a sprint triathlon comprised of a 750m swim, a 20km bike and a 5km run. But maybe the following year, at the same event I did an Olympic triathlon comprised of a 1.5km swim, a 40km bike and a 10km run. Each "event" has this possibility and as I improve and get better I may do different races. I don't want to have records in the event table called Esprit triathlon Ironman, Esprit triathlon Half Ironman, Esprit triathlon Olympic triathlon. This would be repetitive. That is why I created another table called for types.

Basically this is how I want to enter my data and I have created a form that looks like this. I want to enter the race date, choose the event, type age and distance from dropdowns, enter my times and then have it do the calculations for km/hr, min/km etc based on the length of the race.

Thank you

 

[plog]

As for the lookup table discussion--why not make everything a drop down? You could make a table to select hours, minutes, seconds, position and bib. You don't because there's nothing to gain from it, instead all of those inputs allow the user to type in the specific value they want. As your data is structured now, you might as well allow that for the drop downs you have. If you only have 1 real field in a table, that table doesn't need to exist.

For your event example, you wouldn't. In your example you would have one record in the event table (Espirit Triathlon), 2 records in the Race table (1 for each year which right now will store the type--more on that in a minute) and 6 records in the Race table (1 swim per year, 1 bike per year, 1 run per year).

This race table is really the key and the lowest level of data you should be storing. It is where you store the distance of each race, the type (bike, swim, run) and your time. I'd even put a comments field at this level so you can say the water was choppy in a specific race, got a flat tire on a specific bike ride and got attacked by a dog on a specific run. Also, with a table like this it will make it easier to query and compare similar races (e.g. 25km runs to 25km runs--even if the bike and swim distances were different).

As for types, you haven't explained what it means exactly. In your initial post you started out by saying it defined the distances in the triathlon, but then you immediately said that each event defines its own distances differently. The type field doesn't seem to do anything for you.

Lastly, what is the purpose of the age field? Like race types, age groups can be defined race by race (10 year bands, 5 year bands, custom bands). Seems like you could have a ton of predefined bands, probably best just to make that user input field. Plus, you are capturing the event date and you know your birthdate, so you can always determine your age on that date.

 

[timbits35]

Hi,

Thanks again.

I have to admit that I just don't understand what you are trying to explain. I am certainly no expert, a self-taught Access user for the past 20 years. As for ages, since I am a certain age right now and I started 10 years ago competing, there are not that many age categories, so I do not see a problem with having that as a table.

I do have all kinds of other fields in the race table : comments, the weather, if I slept well, ate well etc.

Maybe I will just try to enter a few more fields in the race table that reflect the actual race distance and try to do my calculations like that.