Page 1 of 2 12 LastLast
Results 1 to 15 of 28

Thread: Database Design

  1. #1
    Join Date
    Feb 2005
    Location
    Queensland, Australia
    Posts
    33

    Exclamation Unanswered: Database Design

    Hi,

    Can anyone help me with designing 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!!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sounds like you have got your data model sorted

    The main obseravtions I'd make are each table should have a uniquely identifying value. For computer reasons ideally this should be numeric. Access will normally suggest the Autonumber data type for this purpose. The number normally has no intrinsic value, occasionally it does.

    so I'd suggest thhat you define a column (field) in each table which is an autonumber data type, and is also the prmary key

    eg in tblEventInfo create a column: EventID

    Having done that then include that column as type long integer in the columns which have a relationship. Think of it as a parent & child relationship IE the there may be one parnet for multiple child records (at a single event there may be many races)

    eg having declared tblEventInfo with a column EventID then use that smae column name in each table that 'belongs' to that race
    eg tblRaceInfo should have it
    indeed tblRaceInfo will probably have columns for each of the other tables

    The main wrinkle is going to be if the engine changes between races or events. having declared the columns then open the reraltionships window then click thecolumn int he parent table and drag it to the child table. At this point the advantage of using hte same names will be apparent as access magically identifes the column of the same name. I'd suggest you define the realtionship as an RI link with delete cascade

    incidentally you will find it easier in the long term to identify columns without spaces, its a matter pf personal choice but I would use a mix of upper & loweer case to declare column names eg EventName in place of Event Name, OR eventname

    keep up the good work.

  3. #3
    Join Date
    Feb 2005
    Location
    Queensland, Australia
    Posts
    33
    How does this look,

    tblEvent (EventID, EventName, EventTrack, EventDate)

    tblCar (CarID, CarName, GearBox, DiffRatio, EngineName,)

    tblDriver (DriverID, FirstName, LastName)

    tblEngine (EngineID, EngineMake, EngineModel, EngineCompression, Carburetor, FrontJets, RearJets, FuelPressure, BaseTiming, TotalAdvance)

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

    do i need more tables or is this alright as i do not have a clue

    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you propose to associate a particular race with a specific event
    euaqlly how do you know which engine was used with which race, car or driver for that matter

    Whatever else you vendors may claim computers ain't psychic, some users of my systems may claim the're psychotic, you need to clearly define how these things interrelate. The key principles are that data should be stored in a table with similar data. For example, it is arguable that they tyre size might be an attribute of the car not the race, but that may not be the case I no stuff all about drag racing. If you are repeating information in a table then its a sure indication that there is a problme with the table. For example if the tyres don't/can't change between races then that should nto be stored in the race table, type pressures fine. There is no definitive way of someone else assessing your data model without detailed knowledge of the field.

    A db mantra is that you don't store a derived field, I think it is arguable that you have a derived column in your DB - the MPH values, you could get to either the time, OR the speed knowing the other (I think)


    As a general comment I'd say the model is progressing well, you just need to associate or link the tables so that you can then identify who drove the car with which engine in which race with which settings.

  5. #5
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by healdem
    As a general comment I'd say the model is progressing well, you just need to associate or link the tables so that you can then identify who drove the car with which engine in which race with which settings.
    Agreed, it is very important to think about what relates to what.
    Adding to this (Ive just started Access too ) it pays of to think about what table or object connects all these things to one database. My guess would be the car, since that is the centre of the engine/driver/car. Thinking about this will ease up certain lookup-functions in your forms later (say, if 75% of the time you would start a search in the database with the color of the car, you'd be wise to make the table with the column 'color' the 'main' table)

    It's also interesting to think how about how to connect the event/race-properties, if at all.

  6. #6
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    Just a thought. Each car can have one driver per race (presumably), but if the car enters another race can the driver change? Can a driver enter a race in a different car? If the answer is yes you have a many to many relation between driver and car. In that case I'd suggest another table car_driver or race_driver.

    Good luck.


  7. #7
    Join Date
    Feb 2005
    Location
    Queensland, Australia
    Posts
    33
    Thanks for all your posts, I printed them all out and went through them one by one and came up with an updated database design.

    tblEvent
    EventID (autonumber, pk)
    EventName
    EventTrack
    EventDate

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

    tblDriver
    DriverID (autonumber, pk)
    FirstName
    LastName)

    tblRace
    RaceID (autonumber, pk)
    EventID (Long Integer)
    CarID (Long Integer)
    DriverID (Long Integer)
    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

    Relationships

    tblRace:tblEvent (EventID)
    tblRace:tblCar (CarID)
    tblRace:tblDriver (DriverID)

    All relationships will be Enforced Referential integrity with delete cascade. (what about update cascade )
    Originally Posted by avlan
    it pays to think about what table or object connects all these things to one database. My guess would be the car, since that is the centre of the engine/driver/car.
    I was thinking tblRace as I would like to go to the drags and do a race and have a look at the
    TrackTemperature
    RelativeAltitude
    Milibars
    Humidity
    AirTemperature
    And search previous races for similar conditions so that I can setup the car and engine as to keep Elapsed Time of the car as consistent as possible.(e.g Relative altitude can vary greatly over an event. At 4:00pm the track could have a Relative Altitude of 1089, and at 8:30pm when the air temp and humidity drop off the Relative Altitude could be as low as 700.This can make a big different due to, the lower Relative Altitude means the air is a lot denser (compressed) allowing a lot more air to enter the carby. The same as how nitrous oxide works it is just compressed oxygen therefore more air means the engine can burn more fuel which means more horse power which = lower elapsed times and higher elapsed mph.
    Originally Posted by Ryker
    Just a thought. Each car can have one driver per race (presumably), but if the car enters another race can the driver change?
    your right so for this purpose what about;
    tblCarDriver
    CarDriverID (autonumber, pk)
    CarID (long integer)
    DriverID (long integer)

    Thanks for all your help,Let me know if i am heading in the right direction.
    Last edited by Dean Welch; 02-22-05 at 00:12.

  8. #8
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    there's not really much more 2 say.

    cascading update means .. that if you change an CarID in your Table tblCars all corresponding references in tblRaces will be automatically updated so the entries still point to the same car (though now with another ID). But in your case .. as you've defined all key's as autonumber this doesn't have any effect as autonumber fields cannot be changed anyway.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Dean Welch
    your right so for this purpose what about;
    tblCarDriver
    CarDriverID (autonumber, pk)
    CarID (long integer)
    DriverID (long integer)
    I think this table is supefluous....
    you have already defined the driver AND car in tblrace, this table merely duplicates the information. Potentially it could cause data anomolies, unless you build in some careful data validation. ie if you change the driver in tblRace then you also change it in tblCarDriver. Does this table store anything else?

  10. #10
    Join Date
    Feb 2005
    Location
    Queensland, Australia
    Posts
    33
    Originally Posted By healdem
    Does this table store anything else?
    No just thought it would hold all the info for tblCar & tblDriver as it seems to me i need a many to many relationship.

    Originally Posted By healdem
    I think this table is supefluous....
    you have already defined the driver AND car in tblrace, this table merely duplicates the information. Potentially it could cause data anomolies, unless you build in some careful data validation. ie if you change the driver in tblRace then you also change it in tblCarDriver. Does this table store anything else?
    i don't quite understand this as i am not up with the lingo yet (then again maybe it's not lingo might be just comon sense ), but what i meant was removing CarID & DriverID from tblRace and adding CarDriverID, just thinking ahead for my forms thats all, thought i could use a subform with this data in the race form??.

    or Do you mean you enter the info into tblCar and into tblDriver then you select it again in the tblCarDriver meaning you are effectively entering the data twice? If this is the case what if i kept the tblCar, tblDriver and left out the tblCarDriver and built a frmCar with tblDriver as a sub-form?

    Should i be seeing the DB as a whole Or maybe i should forget about forms for now and just concentrate on Databased design.

    Also one of my mates was looking at my design the other night that does not no much about access but suggested that the race table might not comply to normal forms.(if that makes sense?)

  11. #11
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by Dean Welch
    No just thought it would hold all the info for tblCar & tblDriver as it seems to me i need a many to many relationship.



    i don't quite understand this as i am not up with the lingo yet (then again maybe it's not lingo might be just comon sense ), but what i meant was removing CarID & DriverID from tblRace and adding CarDriverID, just thinking ahead for my forms thats all, thought i could use a subform with this data in the race form??.
    Whatever solution you choose, the idea of a database is that you NEVER have the same info in it twice, EVER. So if you, in the end, have fields which display a car and it's driver, and also in 2 other fields the results of last week where that same driver finished 3rd, both driver-fields should reference to one cell in the database, i.e. that drivers name. If you have entered that name in, say, tableCars AND seperately in tableResults then you have done something wrong. Then, you should make a reference table, which for instance has 2 columns, one ID, one the actual name, and in the results- and car-tables you should have the ID (the REFERENCE) there and not the name.

    This might seem a bit futile, but imagine it being about types of tires: If for instance Bridgestone decides to change its name, you are left with an obsolete database if you entered the name at 500 different places, where if you have a reference table, you only need to change the name of the manufacturer once.

    Apart from that, double entries cause possible confusion for users (different spelling mistakes ) and may cause unmanagable data (you cant make one list with every race the driver finished AND the types of cars he drives f.i. since they're not linked)

    Hope that made sense

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you could have more than one driver in more than one car in one race then having the extra table may make sense. Somehow I don't evisage the driver stopping half way down the drag strip to do a driver change, or leap into another car

    So as I perceive your design:-
    A single driver participates in a single race in a single car, although that driver may drive different cars (or the same car) in different races at the same event. Similarly the same car may be driven by different people in the same event. The race is unique, once the car is entered for that raceoff then the car and driver are uniques for that race (No multiple drivers, No multiple cars)
    if there is only only link per column in the row then a simple direct link would be adequate.

    You will need the extra table if you want to record multiple car entries in the same race. But as your initial request was to record information on your car so that you can set it up correctly in future events you don't need the additional table.

  13. #13
    Join Date
    Feb 2005
    Location
    Queensland, Australia
    Posts
    33
    Originally Posted by healdem
    You will need the extra table if you want to record multiple car entries in the same race. But as your initial request was to record information on your car so that you can set it up correctly in future events you don't need the additional table.
    so what your saying is for example my brother would be racing his car at the same event therefore for him to also use my database i would need this table, is that correct.If so it has been suggested that i add instead of tblCarDriver,

    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.

    Does this make sense,


    also do yous think i need a tblengine to break down tblrace & tblCar (e.g FROM tblRace; Carburetor, FrontJets, RearJets, FuelPressure, BaseTiming, TotalAdvance & FROM tblCar; EngineMake, EngineModel, EngineCompression) or do you think i am heading along just fine.I also thought that i could move the tyre type & tyre size to the tblCar as this won't change from race to race only from event to event if that.
    i have heard of Entity Relationship Models would this make it easier you think to see where i am going wrong?
    Last edited by Dean Welch; 02-23-05 at 17:30.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No you need a redesign of the entire race table
    If you want to store the results of more than one car in the same race then you need to normalise that data. IE split the data into related areas.

    the info on the race eg Air, track tems pressures will be identical for a specific race. the times and speeds relate to a specific car

    So you will need to redesign your race table to strip out anything that realtes to a specific car, and mmove that to a new table which associates the race, driver and car aloing with any details pertinent to that race for that card / driver combination.

  15. #15
    Join Date
    Feb 2005
    Location
    Queensland, Australia
    Posts
    33
    sorry i am at a total loss is it possible for anyone to show me a layout of what they suggest, Please Please Please.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •