5 Replies Latest reply on Apr 6, 2012 8:17 AM by philmodjunk

    Need help with laying out table relationships

    JohnWozniak

      Title

      Need help with laying out table relationships

      Post

      Let me first say that I am a total noob here, so please forgive my ignorance.  I want to up a DB to log Chassis/Tire setups for race cars.  I have already laid out the tables I need for the info I want to log, but I am not sure what the proper relationships between the tables should be.

      Car table; has basic car details e.g. models, year, etc.

      Track table; has basic details on various tracks e.g. track name, location, etc.

      Chassis table; details on suspension setups and corner balancing info

      Tire table; details on various tires e.g. brands, models, sizes

      Pyro table; a log of tire temps after each run

      Here is how the data comes together, for a specific car at a specific track we will use a specific chassis setup with a specific set of tires.  So what we need to figure out is what is the best chassis setup for the tires we are going to use at that specific track on that specific car.  The way we figure out the proper chassis setup is, first we determine what tires will be using at that track, then we set the chassis to generic settings, run the car around the track a few times and bring the car in to record tire temps.  Using those tire temp details we will adjust the chassis, and then do the process over again and again.  We do this multiple times looking for the optimal settings for the best tire traction on the track. 

      I would like to be able to log everything, including the multiple chasis/tire setups used at a specific track for a given car.  Note that we could have multiple cars running at the same track and as well those same cars will be going to other tracks (and the process starts all over again).  Plus we could use different tires at the same track for a different event type.  So you can see why we need a DB to track all of this as it can get complicated quickly.  And to think we do this on paper today (well speadsheets really), uhg.

      Please let me know if more details are needed to help explain further.

      Thanks in advance,

      -JohnW

        • 1. Re: Need help with laying out table relationships
          JohnWozniak

          Let me followup with...  I know that the tire table will be a one-to-many with the pyro table (multiple readings for a given set of tires), but I am not too sure about things after that.  Becuase we could use different tires on the same car and at the same track which would mean a different chassis setup.  So for a given car at a given track with a given set of tires we will use a specific chassis setup.  In otherwords, we'll change the chassis setup for a given set of tires because not all tire brands/models/types perform the same even on the same car at the same track.

          • 2. Re: Need help with laying out table relationships
            philmodjunk

            You actually have many to many relationships and a lot of them. 

            Take your tires. A given Chassis can use many different tires, Yet a given type of tire could be used with many different Chassis. And you have many different tracks and many different "events"...

            Here's the basic set up between two tables in a many to many relationship:

            Chassis-----<Chassis_Tire>----Tires    --< means one to many
            Chassis::__pk_ChassisID = Chassis_Tire::_fk_ChassisID
            Tires::__pk_TireID = Chassis_Tire::_fk_TireID

            The __Pk fields would be auto-entered serial numbers and the matching _fk fields would be number fields. Chassis_Tire is called a "Join" table. Interacting with the data is usually done by placing a portal to the Join Table on one of the other two tables and then including fields from the third table in the portal's row.

            Here's a demo file on Many to Many relationships that may help you get started: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

            • 3. Re: Need help with laying out table relationships
              JohnWozniak

              Thanks PhilModJunk for the reply.  I understood what you described, FYI: I do have a decent working knowledge of Dbs and such, but design is not my strong point, which is why I am here.  As you said, I may have lots of many to many --head spinning--

              I got your file and will look it over later.

              • 4. Re: Need help with laying out table relationships
                JohnWozniak

                You know, when you write these posts everything makes sense, then when someone responds you see the error in your ways :-O

                A few more words to help clarify (or confuse) things...

                For CarA on TrackA with TiresA using ChassisA we will record several iterations of tire Temps(A,B,C, ...). Then we'll change chassis setup to ChassisB (still with CarA on TrackA with TiresA), and record several iterations of tire Temps again.  We'll do this till we get the right Chassis() setup for this car/track/tire combination.

                For a different event at TrackA using CarA we will use TiresB, in which case we repeat the above looking for the right Chassis() setup.  Then we could take CarA to TrackB using Tires() ... Chassis()...Temps().  Or we may run a second car, CarB on TrackA using Tires()...Chassis()...Temps()

                At some point we need to flag the optimum combination of Chassis() with Tires() for CarA on TrackA

                This way at a later date when we go back to TrackA with CarA using TiresA we can look in the DB to find what the right Chassis() setup is.

                • 5. Re: Need help with laying out table relationships
                  philmodjunk

                  I'm thinking your Pyro data may serve as the join table in a "star join" to each of the other tables to specify car, chassis, Track, etc...

                  Your data entry layout might well be based on the Pyro table with drop downs for linking to each of the other tables. Since you'll be creating records where the links are all the same over and over again. (data for each tire and/or each test run...), global fields with drop downs or pop ups can be set up so that you select car, chassis, track, etc once and then auto-enter calcualtions enter these values into corresponding fields in the Pyro table each time you create a new record so that all you have to enter is the new temperature info.