SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2001
    Location
    home
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Design Question

    I'd like to design a database that will store automotive performance data so that the user can compare performance results between various automotive makes.

    The front end will probably be set up with individual makes grouped together with bullets that the user would select.

    For example: Audi, BMW....etc.....and then individual models under the make category would be listed with bullets for the user to select.

    The front end will utilize PHP with a MySQL database.

    What would be the best way of setting up the database in terms of table structure, primary keys, etc.?

    -Rob

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    Well, not knowing EXACTLY what kind of information you will be looking for, I'll give you a simple dbase design to get yourself started.

    ** NOTE ** PK = Primary Key, FK = Foreign Key
    Code:
    Table: Make
        MakeID - Identity field (or whatever MySQL called an autonumber field) ** PK **
        MakeDesc - VarChar(??)
    
    Table: Model
        ModelID - Identity field ** PK **
        MakeID  - Int ** FK to the Make table **
        ModelDesc - VarChar(??)
    
    Table: Performance
        PerformanceID - Identity Field ** PK **
        PerformanceDesc - VarChar (??)
    
    Table: ModelPerformance
        ModelID - Int ** FK to Model table **
        PerformanceID - Int ** FK to the PerformanceTable ** ** PK = ModelID + PerformanceID **
        PerformanceRating - Int (could be char depending on how you choose to rate
    Like I said, I don't know exactly what kind of information you're looking to gather, but this should give you a good framework to get started with. Let me know if you have questions or need and explanation on why I did something the way I did.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2001
    Location
    home
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Dave,

    Thanks for responding. Basically, I'm looking to include the same data as a typical automotive review that you would find in any of the mainstream automotive magazines such as Road and Track or Car and Driver. I wouldn't want to include all the data, just some of it such as price, curb weight, engine specifications (horsepower/torque), and then the performance points: 0-60 mph, 0-100 mph, 1/4 miles time, top speed, lateral accl. etc.

    -Rob

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    The way I gave you would allow for the most flexibility and allow you to add or delete benckmarks at will. You would just add another performance record for a new benchmark, then add the benchmark values to your models. This method also allows you to show the benchmarks you feel are relevant to a model.

    Your other option would be to have a field for every benchmark on the model table and just do them there. This method would have a value for each benchmark for each model, regardless of the appropriateness or not. To add a new benchmark, you would be required to add a new column to the table everytime.

    I personally would stick with what I gave you first, but it's your choice...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2001
    Location
    home
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dave. What would be the difference between the performance and model performance tables?

    -Rob

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    If you add all the benhmarks to the model table, you can eliminate the performance and modelperformace tables.

    If you keep it the way I suggested, then performance holds all the different types of benchmarks (might be a good idea to rename it to benchmark since that's what it really contains). So, for example you would have this kind of information on your performance table.

    Code:
    PerformanceID   PerformanceDesc
    1               Price
    2               Curb Weight
    3               Horsepower
    4               0-60
    Your modelperformance table would then contain which benchmarks are being applied, and the value of the benchmark. So you'd have stuff like this:
    Code:
    ModelID    PerformanceID    PerformanceValue
    1          1                 16,000
    1          2                 13
    1          3                 125
    1          4                 10
    2          1                 60,000
    2          3                 250
    2          4                 3
    3          1                 100,000
    3          4                 8
    As you can see, model 1 had values for all four benchmarks. Model 2 didn't have a curb weight benchmark, and Model 3 didn't have curb weight or horsepower for benchmarks.

    Hope that helps a little.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

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