Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 '13 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 '13 at 15:31
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '13 at 10:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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