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:

GameID (PK), GameName

CarID (PK), Manufacturer, Model, GameID (FK)

TrackID (PK), TrackName

PartID (PK), PartName, GameID (FK)

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.

TimeSetID (PK), GameID (FK), TrackID (FK), CarID (FK), (TimeSetID, TuneID) (FK), (TimeSetID,PartID) (FK), time

(TimeSetID (FK), TuneID (FK)) (PK), value

(TimeSetID (FK), PartID (FK)) (PK)

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

  • 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.
    – mdoyle
    Apr 16, 2013 at 13:28
  • 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").
    – Lennart
    Apr 16, 2013 at 15:31

1 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.

  • 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.
    – Lennart
    Apr 17, 2013 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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