Database Design Help

[dfwcomputer]

Hi,

I would like to design a database to store all the info about a drag racing event including;

• event info (Event name, track name, event date)
• car info (Car name, gearbox type, diff ratio, engine name,)
• driver info (driver firstname, driver lastname)
• Engine info (make, model, engine compression, carburetor, front carburetor jets, rear carburetor jets, fuel pressure, engine timing at idle, total engine timing)
• Race info (track temperature, relative altitude, milibars, humidity, air temperature, tyre type, tyre size, tyre pressure hot, tyre pressure cold, stage RPM, shift RPM, reaction, 60foot time, 330foot time, 660foot time, 660foot MPH, 1000foot time, elapsed time, elapsed MPH, comments.)

My issue is design of tables and relationships. as you can probably see, obviously cars will do many runs at each event, and there engine setups and car setups(diff ratio) can differ each run. there might also be different drivers for the same car. weather info like track temp etc also change from race to race so I think it is possible I might need some sort of race time as-well. My main reason for the database, apart from organizing all my results would be so that I could do a run at an event and search previous races by the current runs weather info (relative altitude, milibars, humidity, etc) as to set the car up similar to keep it’s elapsed times consistent. Please, please, please help as I am at a total loss.

Thanks in advance

[dfwcomputer]

this is the revised design, what do you think,

tblEvent
(EventID, EventName, EventTrack, EventDate)

tblCar
(CarID, CarName, EngineMake, EngineModel, EngineCompression, GearBox, DiffRatio, ,)

tblDriver
(DriverID, FirstName, LastName)

tblRace
(RaceID, Carburetor, FrontJets, RearJets, FuelPressure, BaseTiming, TotalAdvance, TrackTemperature, RelativeAltitude, Milibars, Humidity, AirTemperature, TyreType, TyreSize, TyrePressureHot, TyrePressureCold, StageRPM, ShiftRPM, Reaction, 60ftTime, 330ftTime, 660ftTime, 660ftMPH, 1000ftTime, ElapsedTime, ElapsedMPH, Coments)

maybe i also need to put a car driver table (tblCarDriver) in case different people race the vehicle, What do you think,

tblCarDriver
(CarDriverID, CarID, DriverID)

Thanks

[Rawhide]

Looks good for a first stab at the design. The problem I see is that there's no way to relate anything to the event or the race.

So first let's create a relationship between the races and the events. There will be several races for a single event. We can't add an infinite number of RaceID fields to the Events table nor do we want to add mutliple records to the events table for a single event. Therefore, we need to add an EventID field to tblRace. So the second field in tblRace should be EventID as a foreign key to Event ID in tblEvent.

Next we need a relationship between the race and the drivers and cars. There can be multiple races for driver or car and there are definitely multiple drivers and multiple cars per race. So we need an intermediary table.

The table would be:

tblRaceCarDriver:
RCDID int autonumber not null primary key
RaceID int foreign key to tblRace.RaceID
CarID int foreign key to tblCar.CarID
DriverID int foreign key to tblDriver.DriverID

You can drop tblCarDriver as it is no longer needed. It didn't suit your needs because it defined a driver to car relationship that would create matches that didn't exist.

[dfwcomputer]

I have spent a lot of time printing and reading other posts that seem to apply to my situation, as not to waste your time, and along with your input this is what i have come up with;

Engine type & model would be (e.g Ford, 351 Cleveland) so i put them in a new table (tblEngine). is this what you meant? Also should i have put, EngineCompression, Carburetor, FrontJets, RearJets, FuelPressure, BaseTiming, TotalAdvance in this table or should i have left it in the race table, i also thought tyre type and size could go in tblCar as these probably won't change from race to race only from event to event, so there is no need for them in tblRace.

tblEvent
EventID (int autonumber not null primary key)
EventName
EventTrack
EventDate

tblCar
CarID (int autonumber not null primary key)
EngineID (int foreign key to tblEngine.EngineID)
CarName
TyreType
TyreSize
GearBox
DiffRatio

tblDriver
DriverID (int autonumber not null primary key)
FirstName
LastName

tblEngine
EngineID (int autonumber not null primary key)
EngineMake
EngineModel
EngineCompression
Carburetor
FrontJets
RearJets
FuelPressure
BaseTiming
TotalAdvance


tblRace
RaceID (int autonumber not null primary key)
RaceCarDriverID
TrackTemperature
RelativeAltitude
Milibars
Humidity
AirTemperature
TyrePressureHot
TyrePressureCold
StageRPM
ShiftRPM
Reaction
60ftTime
330ftTime
660ftTime
660ftMPH
1000ftTime
ElapsedTime
ElapsedMPH
Coments

tblRaceCarDriver
RaceCarDriverID (int autonumber not null primary key)
RaceID (int foreign key to tblRace.RaceID)
CarID (int foreign key to tblCar.CarID)
DriverID (int foreign key to tblDriver.DriverID)

All relationships will be RI Link with delete cascade.

i would like to add a race time as-well any suggestions? maybe in the tblRace.

Thanks for all your help,Let me know if i am heading in the right direction.

[kleky]

It may be easier to lay this out in a diagram format know as a ERM (Entity Relationship Model). On this you can specify any one to many ralationships etc. It'd be far easier to see what may be wrong, and it's a necesity when developing databases. Excuse me if your already aware of this

[dfwcomputer]

It may be easier to lay this out in a diagram format know as a ERM (Entity Relationship Model). On this you can specify any one to many ralationships etc. It'd be far easier to see what may be wrong, and it's a necesity when developing databases. Excuse me if your already aware of this

this sounds like a good idea how would i go about this.

[dfwcomputer]

just about ready to build db what do yous think????

tblEvent
EventID (int autonumber not null primary key)
RaceID (int foreign key to tblRace.RaceID)
EventName
EventTrack
EventDate

tblCar
CarID (int autonumber not null primary key)
CarName

tblCarSetup
CarSetupID (int autonumber not null primary key)
CarID (int foreign key to tblCars.CarID)
TyreType
TyreSize
GearBox
DiffRatio


tblDriver
DriverID (int autonumber not null primary key)
FirstName
LastName

tblEngine
EngineID (int autonumber not null primary key)
EngineMake
EngineModel

tblEngineSetup
EngineSetupID (int autonumber not null primary key)
EngineID (int foreign key to tblEngine.EngineID)
EngineCompression
Carburetor
FrontJets
RearJets
FuelPressure
BaseTiming
TotalAdvance

tblRaceVariables
RaceMasterID (int foreign key to tblRaceMaster.RaceMasterID)
RaceTime
TrackTemperature
RelativeAltitude
Milibars
Humidity
AirTemperature
TyrePressureHot
TyrePressureCold
StageRPM
ShiftRPM
Reaction
60ftTime
330ftTime
660ftTime
660ftMPH
1000ftTime
ElapsedTime
ElapsedMPH
Coments

tblRaceMaster
RaceMasterID (int autonumber not null primary key)
EventID (int foreign key to tblEvent.EventID)
CarID (int foreign key to tblCar.CarID)
EngineID (int foreign key to tblEngine.EngineID)
DriverID (int foreign key to tblDriver.DriverID)

All relationships will be RI Link with delete cascade.

Dean

[dfwcomputer]

any ideas guys???

[tysonwright]

In an earlier post kleky mentioned using an entity relationship model. The idea is that entities have one-to-many relationships with each other. If two entities have a one-to-one relationship, it's the same entity. If the relationship is many-to-many, there are entities that have not yet been defined (probably one of the entities you have included needs to be broken up).

Each entity should have a primary key - this is what uniquely defines each record as unique. It should also have foriegn keys - these link particular records from this entity to specific records on the other entities. Again, these links should always be one-to-many.

I am not an SME at drag racing, but it looks like you're mostly going the right direction with this. I do wonder about tblRaceMaster. What does this buy you that just having foreign keys on tblRaceVariables wouldn't? Unless I'm missing something it seems like you are just repeating the keys on another table. (In other words, just quickly looking at it gives me the impression that tblRaceMaster and tblRaceVariables have a one-to-one relationship.) Just put the foreign keys on tblRaceVariables. If the entity for both these tables is a Race, the variables and the foriegn keys are all just attributes of that race and should be on the same table.

Personally, I hate having first name and last name as separate fields - it seems like I'm always writing expressions to concat these. There may be reasons for them to be separate, but I don't often come across them.

I wonder whether your carbs should be a on separate table? Will you have one carb setup that gets used across multiple engine setups? (This isn't a rhetorical question, I just don't know.) I also wonder whether the Race variables table should be broken up - separate the weather conditions from the race results. Maybe, maybe not, just something you may want to think about.

Final comment is that you need to keep in mind that the more normalized you make it, the more flexible it will be for reporting, but it also makes that reporting more complex. You may want to design view queries that denormalize it in ways that speed up the process of designing ad-hoc reports. That way if you're at the track and want to run some random ad-hoc the basic joins and linking are all in place.

Hope this helps,

Tyson