0

This is not a code review but more of a database schema review.

I have a sports events website that I am doing in my spare time. I need assistance with the database design. I am about 80% completed. It sounds simple, but it’s more than just the event name and start and end dates.

Let me give you the scenario. I am busy developing a website where I want to display different types of events on this website like mountain biking, road cycling, running, trail running, triathlon, biathlon, etc on it. The reason behind me wanting to do this is to keep my skills up to date and let me experiment with technologies that I don’t normally use at work. I am a mountain biker myself and sometimes it becomes a mission looking for events because every event has its own website. So I thought about creating a centralised database and website to list all these types of events.

The more detail I want to display per race the more complex the database becomes. I created the first table and called it Events. And event has only a name, like:

  • Die Burge MTB Challenge
  • Resolution Health Ride the Rhino

The Die Burger MTB Challenge is a mountain bike race that takes place on 1 day. There are different distances that a rider can enter, for example 75km, 60km, 50km, etc. Only individuals can enter, no teams are allowed. Each distance has its entry fee. The start and end venue are at the same venue.

The Resolution Health Ride the Rhino takes place over 3 days. There are different categories of difficulty available for a rider to enter, namely the Black Rhino, White Rhino and Junior Rhino. All 3 categories takes place over 3 days. The Black Rhino consists of the following distances, 80.7km, 89km and 90km. The White Rhino also consists of 3 shorter distances as well as the Junior Rhino. Individuals and teams can enter for the Black Rhino and White Rhino. Only teams can enter for the Junior Rhino. The fees are per individual or team, for example R3800 for an individual and R7600 for a team. There are early bird entry fees and late entry fees. There are also different packages that you can choose, like All Inclusive package that contain a lot of stuff like accommodation, food, showers, etc and a self-catering package where you need to organise your own accommodation. The start and end venues are different each day. Day 1 point start at Venue A and end at Venue B. Day 2 might start at Venue B and end at Venue B, and Day 3 might start at Venue B and end at Venue C.

So I broke it down to create a another table called Races. This would include something like “75km Route” for the one day event or “Black Rhino” for the multi day event. I broke it down even further and added a disciplines table. The disciplines table just tells you what you will be doing on the day. For example, the race type might be triathlon, and triathlon consists of swimming, cycling and running.

This is what I currently have. I know I am missing lots, but if you could maybe guide me on the right path then I would appreciate it. Please feel free to chop and change as you like.

enter image description here

migrated from codereview.stackexchange.com Jul 21 '14 at 12:37

This question came from our site for peer programmer code reviews.

2

Clearly you need a "Competitor" table if you wish to represent them, which I assume you do! Competitor Name, Age, Sex, Contact etc.

Personally I would create a new table called "Discipline Type" which would differ from "Race Type". The two are functionally independent - race type could be "Triathlon" while "Discipline Type" could be "Swimming"/"Running"/"Road Cycling".

Edit: Sorry, since you wish to include teams, a team will represent a group or a single competitor. Therefore you probably want an "Entry" table which represents a person or Team entering a race Entry would have "Race ID", "Competitor ID" (these two your PK, required), "Team ID", "Team Order", "Team Captain" (binary field, last 3 fields not required). This would then be able to represent individual entries AND team entries. The "Team ID" would link to a "Team" table and would have "Team Name", "Support person contact" etc. Note that the team info is NOT required, so that you can have the same Entry table represent a single entrance from a team or a single entrance from 1 person.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.