How best to create this..

[Mantaii]

Hi!
I have posted a couple of times already about a database which I am building for my local running club but I am thinking about starting from scratch rather than just making changes here and there when I hit a wall.

Here is what I am thinking of doing.

Have 2 tables.

1) tbl_runnerDetails
2) tbl_raceResults

(tbl_runnerDetails) - ID, runnerName, DOB, joinDate

(tbl_raceResults) - ID, runnerName, startTime, finishTime, officialTime, finishPosition, timeOnly, marshallDuties, date


I would then create a report that pulls all the information from the tbl_raceResults.
The officialTime would be a query as it subtracts the finishTime from the startTime. I would like the finishPosition to be automatically calculated but it would need to be stored somewhere so I can add up their positions over the year.

I need to record when they joined the club as if they join half way through the year they will be awarded 50 points for not running previous months, this also goes for non-runners i.e. people who are members but have maybe missed a months race.

I think I need to start simple and then build in a lot of the functionality later on but I want to make sure I have the database design correct before I go much further.

Can help would be great and I am more than happy to answer any questions people might have to help them understand what I am trying to do.



edit: I was going to have 2 tables, one for month and one for year but I have just found the calander tool which should do what I need it to do.

[Mantaii]

Ok, gone back to the original plan of having 3 tables.

tbl_runner, tbl_results, and tbl_raceDate

(tbl_runner)
runnerID
name
dob
joinDate

(tbl_results)
resultsID
runnerID
startTime
finishTime
officialTime
finishPosition
timeOnly
marshalDuties
dateID

(tbl_raceDate)
dateID
month
year

I think that is all I need just now!

[Mantaii]

Final change! Its the date that is causing me the issue because I want to have a list of months and a list of years, there will be a one to many relationship between them, one month - many years.

so....tables currently look like

tbl_months
tbl_year
tbl_results
tbl_runner

Obviously Im going to need to have a one to many relationship between the runner and the results as well, since one runner can have many results.

This is giving me a headache

Sorry for my waffle, just helps to type my thoughts up and get them on paper than knocking about my head.

[RayMilhon]

I would recommend that the tbl_Racedate have 2 Fields
RaceID
RaceDate

You can get the Month and Year calculated when running your queries. Also in your definition what happens if there are multiple races in the same month?

[Mantaii]

Hi!
There is only ever one race per month, we run this race on the last tuesday of each month so that isnt a problem. I suppose I should maybe look at making it so it can handle multiple races per month - hmmm. More to think about.

The RaceDate, would that just be entered via a DatePicker or something like that?

[Mantaii]

Ive attached a screen shot of my database - would anyone be kind enough to give me their thoughts.

Thanks,

[RayMilhon]

Depending on the Version of Access in Access 2010 a textbox with a date format includes the date Picker. Not sure about 2007. Prior to 2007 you would use a date picker.

On your Database Diagram I would remove the table tbl_racedate and change the DateID in table tbl_raceinfo to racedate. You are not going to have multiple races on the same day so making it another table is a waste. Other than that it looks good.

[Mantaii]

Thanks!
Will make the changes suggested.

At work I am on Access 2003 but at home I hope to be running 2010 soon! So compatibility could be an issue. I will need to choose which enviroment I use I guess. Probably best to use 2010 as it will be more common in the future if I need to pass the database over to anyone else to look after.

A question, if I have a runner who for example runs 2 races and isnt a member but then on the 3rd race is a member, how would I record this?

I did think the member tick box would work in results but I don't think that will do it. Their results should only count towards the championship once they become a runner but they are more than welcome to run the full series without being a member but their points dont count but will be counted just for their own records. Does that make sense?

O.

[SteveH2508]

Access 2007 has a built-in date picker.

I'm curious as to what a couple of your fields are.

Guntime - is that the time the race started (which I would assume is the same time for everybody?) - if so it should be in tbl_raceinfo.

Is officialtime a calculated field from guntime and clocktime or does some other factor come into it?

What is clubmember? Surely that is info about the runner rather than the result of the race?

What are marshallduties?

[Mantaii]

Guntime - Because this is a handicapped race, everyone starts at different times, slowest runner first with the fastest runner last.

OfficialTime - this is calculated by subtracting the finishTime from the gunTime

ClubMember - Is whether they are a member of the club or not, they could be a non-member for 1 race but then the following month be a member hence the reason it was in with the raceResults.

MarshalDuties - means that that person is a timekeeper for that race and so will be given an average score of the series rather than getting 50 penalty points (if they missed the race).

Hope that helps?

[RayMilhon]

Looking at those responses you can lose the Official Time Field. Since your storing the Finish time and the Guntime Official Time can be calculated so it does not need to take up space.

[Mantaii]

Brilliant, thanks for all your help.

I think I have covered every angle now so I will start to make some forms, no doubt I will be posting in the queries and reports sections of the site soon enough!

Final database layout.....any other feedback is obviously welcome!

Just noticed I must have moved the member field over to the runner last night - Im still in two minds where to put this, should it be with the runner or should it be with the results. I need to be able to see if they are a member but I also need to know from what month they have been a member.....any ideas?

[RayMilhon]

That depends If you place it in the runner table then Say the May Race he is not a member then in the june race he is a member. Now when you run a query/report for May that includes the member field he will show as a member. By keeping it in the race table you can tell whether he was a member or not by race.