SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Thread: Database Design Question
-
Apr 10, 2002, 07:03 #1
- 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
-
Apr 10, 2002, 07:42 #2
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 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
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
-
Apr 10, 2002, 07:48 #3
- 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
-
Apr 10, 2002, 10:50 #4
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 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
-
Apr 10, 2002, 11:04 #5
- 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
-
Apr 10, 2002, 11:52 #6
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 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
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
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