Table Structure for Gear Ratios 1

[a98dsu98sad8sua]

Hi,

I am creation a table with gear ratios, the struction of my source data is as thus:

Gear Number | Gear Ratio | Top Speed in Gear
1st | x.xx | xxx
2nd | x.xx | xxx
3rd | x.xx | xxx
4th | x.xx | xxx
5th | x.xx | xxx
6th | x.xx | xxx
7th | x.xx | xxx

Final Drive x.xx (opt x.xx)
Reverse x.xx
Shifter, Stick, etc: Stick, 5 speed
Shifting Speed: Medium Slow

I'm stuck on how I would present the gear ratios etc in an Access table? Obviously have a column for each gear would be bad database design.

Any help you could give would be most appreciated.

Thanks very much.

Toby Rodriguez

 

[johnherman]

This is a classic case of a relation, bridge, associative table.

You have a base table - not quite sure what that is, maybe transmission or engine identification.

Within the Transmission table, there would be some attributes about that transmission, like shifter, shifting speed, etc. And maybe reverse and final drive.

They you would have a Transmission Gear table which would have the Transmission ID and a Gear number with the specs for each gear (bottom speed, top speed, ratio).

So the tables look like:

Transmission Table
Transmission ID
Transmission Name or Description
Shifter
Shift Speed
Number of Gears
Reverse (or is this another gear - your choice)
Final Drive (or is this another gear?)

Transmission Gear Table
Transmission ID
Gear Number
Gear Ratio
Top Speed
Gear Type (Ordinary, REverse Final, for example)
etc.

Hope this helps.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[a98dsu98sad8sua]

Hi,

Thanks for the help, this is my table structure at the moment, but without the gear ratios, thanks for reading, it's quite big!

tblVehicles (This is where 'pkCarID' comes from, this is my main table)
pk, txtVehType, txtManfacturer, intYear, curCost, txtPrimaryCountry, txtSecondaryCountry, txtModel, txtSubMode, oleVehiclePic

tblBrakes
pk, pkCarID, txtABS, txtStabCont, txtYawCont, txtBrakeBehavior

tblChassicTuning
pk, pkCarID, intWeightDistributionFront, dblWheelbase, intTrackFront, intTrackRear, txtCofG, txtDragCoefficient, dblFrontalarea, txtDownForce, txtDrivelineType, txtLimitedSlip, txtSuspensionSetup, txtTCSSystem

tblCountries (List of Country Options)
pk, txtCountry

tblDownforce (List of Options for Downforce field)
pk, txtDownforce

tblDriveLineTypes (List of Options for Driveline field)
pk, txtDriveLineType

tblEngineConfig (Engine Configurations)
pk, txtEngineConfig

txtEngineLocs (Engine Locations)
pk, txtEngineLoc

tblEngineTuning
pk, pkCarID, intEngineSize, dblPublishedPower, dblPeakTorque, intRPMPeakPower, intRPMPeakTorque, intRedline, txtEngineType, txtCylinderConfig, txtConfiguration, txtEngineLocation, intTurboRevMin, intTurboRevMax, intTurboBoostPRessure, oleTorqueCurve (Pic of torque curve)

tblEngineTypes (List of Types of Engine)
pk, txtEngineType

tblGearTuning
pk, pkCarID, dblFinalDrive, dblReverse, mmShifterStick, txtShifterSpeed

tblManufacturers
pk, txtManufacturers

tblModels (Table with model names in, there is a submodel field so best to keep these in a table so they can be selected again
pk, txtModels

tblNotes
pk, pkCarID, mmNotes

tblOverView
pk, pkCarID, txtBriefHistory, txtReferences

tblPerfSummary
pk, pkCarID, mmDrivingTraits, dbl060, dbl100, dbl150, int600slalom, int700slalom, dblTopSpeed, txtGearAeroLimited, dblQuarterMileTime, dblQuarterMileSpeed, dblStandingMileTime, dblStandingMileSpeed, dbl600, dbl700, dbl800, dbl200SkidPadG, dblSkidPadG

tblSuspensionSetup (List of values that can be used in suspension table)
pk, txtSuspensionSetup

tblTyres
pk, pkCarID, dblFrontSection, dblFrontAspect, dblFrontRim, dblRearSection, dblRearAspect, dblRearRim

tblVehicleTyres (This is a list of types of vehicle, Car, Bike, Truck, etc)
pk, txtVehType

 

[a98dsu98sad8sua]

Thanks, a helpful post.

I have got a separate table with my final drive, etc as you can see (tblGearTuning)

What I have done is created another table (tblGearInfo) with these columns:

pk, pkCarID (the pk of tblVehicles), intGearNumber, dblGearRatio, dblGearTopSpeed.

Sound right?

Does my structure look ok please?

Thanks,

Toby

 

[johnherman]

Yes, looks good. I agree to separate reverse and final. If the transmission was for something that had multiple reverse gears, then the alternative solution I mentioned would be better.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[a98dsu98sad8sua]

Thanks for the input Glad it looks good, taken me ages!

Just need to do the relationships now!

I've reverse gear in a seperate table (As you can see from my example) this is because I don't think I will encounter vehicles with multiple reverse gears, and I'm only really interested in the gears that go forwards