Referencing multiple tables from a single table and store additional data

Question

I have a database where I want to store lap time information from a racing game. I have tables for car, track, game, tuning and upgrade data and want to put all these together to create a single dataset, so that I can e.g. display all times for a track, or detailed information about a lap time on a specific track in a specific car.

My schema is:

Game
-------------
GameID (PK), GameName
Cars
-------------
CarID (PK), Manufacturer, Model, GameID (FK)
Tracks
-------------
TrackID (PK), TrackName
Parts
------------
PartID (PK), PartName, GameID (FK)
Tuning
-----------
TuneID (PK), ElementName, GameID (FK)
TrackGame (-- A Track can appear in multiple Games)
-----------
TrackID (FK), GameID (FK)

To achieve that, I created a table TimeSet where I want to put everything together, but since a car can have multiple Parts installed, and many different Tuning values, I'm having a hard time integrating this properly into a TimeSet. I though of splitting the data over three tables (see below), but I don't think this looks like a good solution, because inserting data cleanly will probably be difficult to do.

TimeSet
-----------
TimeSetID (PK), GameID (FK), TrackID (FK), CarID (FK), (TimeSetID, TuneID) (FK), (TimeSetID,PartID) (FK), time
Time_Tune
-----------
(TimeSetID (FK), TuneID (FK)) (PK), value
Time_Part
-----------
(TimeSetID (FK), PartID (FK)) (PK)

What is the proper way to do this? I'm working with SQLite 3 on Android.

• mdoyle: Do parts and tunings apply to specific cars at specific times? Do certain parts and tunings exist only in certain games, but cars exist across multiple games? I don't fully understand the system you are trying to describe. At first blush it seems like you might want a table that puts together a car with its tunings and parts on a specific date, record that date in TimeSet, and that should handle it. I rather suspect I'm missing something, though.
• Lennart: 1) Yes. 2) No, I have cars, parts and tuning in my database per-game. There may be duplicates (e.g. two VW Golf in two different games), but they would have a different GameID, so they are still unique. Only tracks can appear in multiple games, since they are about 50% identically between games (The reason why TrackGame exists). The "date" (as in dd/mm/yy) isn't required, though it sounds like a good idea to add it. By "time" I mean in all cases "lap time" (as in "I needed 1:21:944 around the track").

Answer 1

I think what you're missing is a car_configuration table. This will have an FK to the cars table and have car_config_parts and car_config_tunings as children, like so:

create table car_configuration (
 config_id integer primary key,
 car_id integer references cars (car_id)
);
create table car_config_parts (
 config_id integer references car_configuration (config_id),
 part_id integer references parts (part_id),
 constraint car_config_part_pk primary key (config_id ,part_id)
);
create table car_config_tunings (
 config_id integer references car_configuration (config_id),
 tuning_id integer references tuning (tuning_id),
 constraint car_config_tuning_pk primary key (config_id, tuning_id)
);

You can then include the config_id as a reference in the timeset table (with no reference to cars). You'll need to ensure that you can't change the parts and tunings for a configuration or implement some form of versioning on this table to ensure you can reconstruct the exact details used to set a given time in the past.

• Lennart: Thanks, seems that my initial idea wasn't that far off. The configurations in a timeset row will be read-only after they are added, since it doesn't really make sense to alter them after you did a lap with a specific setup. New laptime = new data record.