Database Design Question

[RobAC]

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

[DaveMaxwell]

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

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.

[RobAC]

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

[DaveMaxwell]

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

[RobAC]

Thanks Dave. What would be the difference between the performance and model performance tables?

-Rob

[DaveMaxwell]

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.

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:

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.