Database Structure for Sport Event Results

I want to design a sql database. Basically u user logs on and can view their events they have done and which year the result was. For example a user has done the Mountain Cycling event in 2008, 2009 and 2011. And he has done the Road Marathon Event in the year 2006, 2008 and 2010. And the user logs onto the site and can view his results from the different events and years he has done. I need to draw up a SQL structure 开发者_运维百科for this.

Any help would be appreciated

---

Yes, Thank you for the answer. I have come up with a similar structure. But the problem is each event will have occurred in multiple years with different results. For example The Mountain Cycling Event happens yearly with different users and different results. I need a structure where everything is is dynamic. For example Bob logs on. He selects to see all the events he has done last year (2010), Mountain cycling, Marathon, and the results he got from those events he has done in 2010. In the Event Table If You have a Road Cycle Event in the name with the date 2010 and a Mountain Cycle event with the year also in 2010 it would duplicate. Sorry for the confusion. But to make the system dynamic and the user being able to see for example only view the results he got from Mountain cycle event from different years or see all the events he has done in a specific year and the results.

---

You could potentially use 3 tables:

Event

• EventID
• EventName
• Date

User

• UserID
• Username
• Password

Results

• ResultID
• EventID
• UserID
• Result (time taken or whatever)

You might not need the ResultID field in the Results table, you could potentially get away with a composite key using EventID and UserID but I personally prefer to use Primary Keys.

My solution also assumes that the result of all these events are of the timed sort (so they're all 'you did it in x minutes' rather than 'you won 2 nil').