Help With Database Design

Local time
Today, 20:05
Joined
Feb 8, 2005
Messages
92
I have been reading other posts, and this where i should have started from the beginning,I am not very experienced with access (or forums as yous propably know) but can learn anything in no time (hopefully).

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!!
 
Last edited:
With the way you discribe the race and engine tables, you may end up with a many-many relationship between them, and that means you need another table between them (RaceEngine) to keep everything proper.
Just my thoughts, but I would consider removing the engine info table. The fields in that table would be split between the Car table and the Race table - if the variable is changing (timing, fuel pressure, etc), then put it in the race table. If the variable is basically constant to the car (make, model, compression, etc) then put it in the car table.

Before you say -what if I change the engine..if you do, create a new entry in the car table - ei 67 Mustang becomes 67 Mustang ver2.

With the four table set-up, the database becomes quite simple to work with. You have 1 main data table - race and 3 related tables (driver,car,event)
 
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
 
I think you have it right.

You did mention possible inclusion of tbleCarDriver to cope with the many-many relationship between car and driver.

Inclusion of this table would be proper in cases where you need integrety between car and driver tables - without the race. I am not sure what you plan on pulling from this system, but if you have additional uses for this database on top of race results analysis - such as scheduling drivers and cars for future events - queries that would use these tables without a race entry, then your CarDriver table is required.
But, on the other hand, if I assume that you would not have a car/driver relationship without a race, then the CarDriver table is not required since the Race table will be the one-to-many relationship table between them.
 
You need more than you have.

In the Car Table you have engine type and model. You need a new table for each of these and only link the model back to the car table.

The Race Table is a mess and does not meet first normal form - it has repeating groups - and needs broken down in to smaller, more appropriate, tables.
 
Originally Posted By SJ McAbney
You need more than you have.
In the Car Table you have engine type and model. You need a new table for each of these and only link the model back to the car table.
The Race Table is a mess and does not meet first normal form - it has repeating groups - and needs broken down in to smaller,more appropriate, tables.

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 (int foreign key to tblRaceCarDriver.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.
 
Last edited:
Trying to get a handle.

Does it go in this order ?

1.. Event
On event day you can have many runs.

2.. Car
You only have one car, one engine, one driver ?

3.. Runs
Between each run you change the set up of your car as well as monitor weather conditions. So you need to record the set up as well as weather.


Is this database going to be designed for multiple entries each race day or will it be for one particular car.

If it is going to be for multiple entries I would change the table structure to suit.

It may go like this:

Event.. Class.. Car/Driver.. Race(Set up & weather)

One event can have many classes

Each class can have many cars

Each car can have many runs.

As you can see, thinking about it like this will help design the table structure.

Dave
 
Next..

In the engine side of the relationships, will the engine set up change between races or will it stay the same for the whole event. If it MAY change, then the details will need to be related to each race somehow. (Either related to the race table or car table)

Dave
 
Here is a detailed explanation of what I am after, Hope this helps.

The purpose of this database will be to store race results for drag cars, this will include specifications of the cars setup, engine setup, race results (including tracks weather info provided on slip at the time of the race), driver, event . there is normally an event twice a month at the drags i want to be able to go to the drags do a race and record the results on the time slip these results include, Race Time, Car Number (your allocated a number at the event, so there always different), TrackTemperature, RelativeAltitude, Milibars, Humidity, AirTemperature, Reaction, 60ftTime, 330ftTime, 660ftTime, 660ftMPH, 1000ftTime, ElapsedTime, ElapsedMPH. I also want to be able to record ,TyreType, TyreSize, GearBox, DiffRatio, EngineCompression, Carburetor, FrontJets, RearJets, FuelPressure, BaseTiming, TotalAdvance, TyrePressureHot, TyrePressureCold, StageRPM, ShiftRPM, so that when i changed the jets in the carby (or other engine or car specs) between races i know what difference it made.I also need event info as there are multiple tracks across australia, car info to be able to identify the car, and driver info so I can keep tabs on driver and what car they raced at what event and which race at that event. there could also be different drivers for the same car at the same event,(e.g i could do a run in my brothers car). The database needs to be setup for multiple entries (e.g many cars at on event) as many drag racers have shown an interest, (Thanks to my big mouth brother??? :rolleyes: )

Main outcomes for the database will be to record a cars race results for a particular event to keep track of the cars performance, and so that I can search back through previous races by weather info, and setup the car and engine the same so that the car keeps running consistent race times, because the relative altitude, air temp, milibars, race track temp can make a big difference in the overall race time & mile per hour.

I have a good understanding of access and for anything else i could go ahead and build it, but this one i just can't seem to get my head around. All i need is the tabl layout anfd relationships as the forms and everything else i can setup myself.

Thanks,
Dean
 
could somebody please type out how they would lay this dastabase out.
Please, please, please, please, please!!!!
 
Additional food for thought

Here are a couple of other things to think about:

Altitude probably relates more to the Event (ie track) rather than the individual race. Might want to create a Track table, which links to the Event table

Race table has no link to event. Maybe change the RaceCarDriver table to RaceMaster [Race#, EventID, CarID, EngineID, DriverID]. Can then take EngineID out of tblCar.

Add a RaceVariables table to cature all the race info you have listed in TblRace, removing RaceCarDriverID.

How will you track changes to the car and engine over time? You will have no ability to know what the Car/Engine setup was on a previous race day, as only the current record is available. Any time a change happens to either the engine or car, you will need to track that with a new ID, so maybe add a flag on those tables to show they are old, so they are not choices for the RaceMaster table. (hope that was coherent). Effective dating would be another posibility, though that would add a lot of complexity.

Cheers
 
Originally Posted By Jafa
Altitude probably relates more to the Event (ie track) rather than the individual race. Might want to create a Track table, which links to the Event table

Altitude actually changes every run it is listed on your timeslip.
the timeslip you recieve after a run has the following info;
time of race, event date, reaction, 60foot time, 330foot time, 660foot time, 660ft mph, 1000foot time, elapsed time, elapsed mph, relative altitude, milibars, air temp, track temp, humidity. (does this help)

This the new layout. Is this better?

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

tblRaceVariables
RaceID (int autonumber not null primary key)
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.

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

How will you track changes to the car and engine over time?
not sure can anyone suggest something?
(what about a simple RaceTime in the tblRaceVariables???

Thanks in advance,
Dean
 
Couple of problems with new tables

Thanks for the clarification on Altitude, it would belong with the other race variables.

RaceID should not be on the Event table. Race time could be added to the RaceMaster.

RaceVariable and RaceMaster need to be linked by RaceMasterID. As RaceMaster and RaceVariable will have the same records, just different info, they can have the same key, rather than both being autonumber. Does RaceMasterID need to be auto number, would suggest something that is meaningful (maybe some combination of Event-Date-Actual Race Number)

Do you have this in a db in draft already?

Cheers
 
RaceVariable and RaceMaster need to be linked by RaceMasterID. As RaceMaster and RaceVariable will have the same records, just different info, they can have the same key, rather than both being autonumber. Does RaceMasterID need to be auto number, would suggest something that is meaningful (maybe some combination of Event-Date-Actual Race Number)

don't quite understand this? But, Sounds like a good idea how would i go about this?

no dont have a sample db yet as i have built about 10 but were all wrong so thought i wopuld get it sorted out first.

Dean
 
On tblRaceVariable, don't use autonumber, use the RaceMasterID for the key.
 
Ok, here is the new layout!

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

tblRaceVariables
RaceMasterID (int foreign key to tblRaceMaster.RaceMasterID)
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.

How am i looking now?
 
You don't need RaceID in the Event table. That r'ship is in the Master table
Otherwise it looks much better.
Good luck
 
The Final Layout

Ok, The final Layout,What do ya's think?

Don't hold back on the criticism!!!!


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

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
 
Split up car table and engine table as some of you suggested!

Ithink i am just about ready to start building database?, 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.
 

Users who are viewing this thread

Back
Top Bottom