SQL Design Help

Xon

Xon

Associate
Joined
18 Oct 2002
Posts
1,056
Location
Malta
Hi all

Me and a group of friends meet up and race F1 online every week. Therefore, we run a season 4 times a year that last 3 months (12 rounds).

I'm trying to bring our point scoring system to the modern world. I've never designed a db myself and I'm using this as an excuse to learn.

Basically, the format is for each season we have around 10 drivers and we race 12 tracks.

To start off, I'm thinking that my DB will need to have a table with the driver names and a table with all the tracks available (we mix it up but the table will hold all 26 tracks).

Next up I guess I'll need to have a table with the season data:
which tracks were chosen;
which drivers are in this season.

However, I'm stuck at the next stage. Do I need a table for every race? How do I score keep? For each race in the 12 race league I'd need to give each driver the respective points.

(No need to get into Input methods - for that I am covered).

Any help? Or a nudge to the right forum where I should ask this?
 
Permabanned
Joined
9 Aug 2009
Posts
12,236
Location
UK
Tables [Columns]

- Players [Id, Name]
- Tracks [Id, Name]
- Seasons [Id, Name]
- Races [Id, SeasonId, TrackId, DateTime]
- RaceResults [Id, RaceId, PlayerId, Time]
 
Associate
Joined
8 Oct 2020
Posts
1,206
The following setup will work, but be aware that a normalised database isn't always easy to manually insert data into; you'll need to write and save some queries to help generate the data more easily. Alternatively, you can denormalise/flatten it a bit if you aren't comfortable with the structure.
  • Driver [DriverID, Name, etc.]
  • Track [TrackID, Name, etc.]
  • Season [SeasonID, Description, DateStart, DateEnd, etc.]
  • PositionPoints [PositionPointsID, Position, Points]
  • SeasonTrack [SeasonTrackID, SeasonID, TrackID]
  • SeasonDriver [SeasonDriverID, SeasonID, DriverID]
  • RaceResult [RaceResultID, DriverID, SeasonTrackID, FinishPosition, ResultDate]
 
Soldato
Joined
21 Jul 2005
Posts
16,751
Location
N.Ireland
Yeah with the Boyce-Codd normal formists your only going to forget about it! ;)

Depends on what container your going to store in you could even go NoSQL.

Keep it as simple as possible and like @Mesai above mentioned you need to be able to enter is without it soaking up tons of time or it becomes a chore.
 
Soldato
Joined
28 Sep 2008
Posts
13,922
Location
Britain
Honestly, and this goes against everything I've ever said about Excel, but use Excel. Otherwise you need to be looking at CRUD, PHP, MySQL or using an API to plug in to your results and ingest the data directly from your game (probably not impossible and removes human error).

PS, can I join?
 
Associate
Joined
8 Oct 2020
Posts
1,206
Honestly, and this goes against everything I've ever said about Excel, but use Excel. Otherwise you need to be looking at CRUD, PHP, MySQL or using an API to plug in to your results and ingest the data directly from your game (probably not impossible and removes human error).

PS, can I join?

Excel is a perfectly reasonable option. You can use PowerQuery for data manipulation (built in to Excel) and very easy to do visualisations from it.
 
Soldato
Joined
28 Oct 2006
Posts
12,351
Location
Sufferlandria
It's not going to be that difficult to update and if it's just an excuse for learning to use a database, it'll be a good place to start.

Assuming that you're using Mesai's table structure above:
There will be some initial setup required for each season (an entry in the season table, season tracks table, maybe some changes to drivers)
After that, it'll only need updates to the raceresult table each week.
 
Soldato
Joined
21 Jul 2005
Posts
16,751
Location
N.Ireland
Better still use Google Sheets, its easy to share and you can lock it down. You can use SQL like API behind it if you want to do some clever calcs etc.
 
Top Bottom