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.