SQL Design Help

Xon · 4 Jan 2021 at 18:46

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?

Randomface74 · 4 Jan 2021 at 18:57

Tables [Columns]

- Players [Id, Name]
- Tracks [Id, Name]
- Seasons [Id, Name]
- Races [Id, SeasonId, TrackId, DateTime]
- RaceResults [Id, RaceId, PlayerId, Time]

Mesai · 4 Jan 2021 at 19:17

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]

gpuerrilla · 4 Jan 2021 at 20:51

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.

Django x2 · 5 Jan 2021 at 10:22

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?

Mesai · 5 Jan 2021 at 10:34

Django x2 said:
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.

touch · 5 Jan 2021 at 11:01

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.

gpuerrilla · 9 Jan 2021 at 17:00

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.