• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3016
  • Last Modified:

MySQL vehicle database schema

I'm writing a car inventory application and need some advice on a database schema with proper normalization. Here are the requirements of my database:

- Vehicle has a year/make/model/trim.  
- Once user selects a year, the application populates all the makes available for the year.
- Once the user selects a make, the application requests models for that year/model.
- Once the user selects a model, the application populates trims for that year/model/make.
- Finally the user can just select a trim from the select box.  

Also, the vehicle, which should have a unique ID in the database, will have options such as photos, doors, engine attributes that I can put in the database.

The schema alone is what I could use some help on.

Thank you!
1 Solution
You would need a Table to hold the possible combinations of Make, Model_Year and Model (with Model_Year_ID as Primary Key).  You would also need a separate table to hold all the possibilities for Make, Model Year and Trim accessories (with Make_Model_Trim_ID as Primary Key).  These two tables would NOT include the Vehiclr ID.  YOu would then have fields in the main vehicle table to hold the Make_Model_ID, and a second field for Make_Model_Trim_ID.

in clear, it could look like this;

 - year (can effectively be the pk - makes querying more simple, and an 'int' is an 'int'! - then because of this, you effectively don't need a years table really!)

 - id (pk, auto_increment)
 - name

 - id (pk, auto_increment)
 - make_id (pk, FK refs makes.id)
 - year (pk)
 - name
 - rrp
 - ...

 - id (pk, auto_increment)
 - name
 - description
 - rrp
 - model_id (pk - FK, refs models.id)
 - trim_id (pk - FK, refs trims.id)

Populate the years drop down;
SELECT DISTINCT year FROM make_model_year;

Populate the makes drop down (will only pick makes for which we have models - the join can be removed if we don't need this);
SELECT DISTINCT makes.id, makes.name FROM makes JOIN models ON make_id = makes.id WHERE year = <selected_year>;

Populate the models drop down;
SELECT models.* FROM models WHERE year =  <selected_year> AND make_id = <selected_make_id>;

Populate the trims drop down;
SELECT trims.* FROM trims JOIN models_trims ON trims.id = models_trims.trim_id WHERE models_trims.model_id = <selected_model>;

Obviously this db design is arguable. it's based on the following assumption: "models can have different trims/accessories each year". This explains why I have dropped the makes_models_years table suggested by Arthur (above).

Also, about your pictures I'd add a pictures table with a FK to models. you can store the picture in the db (as a blob) or on the hdd, storing the picture name in the db.

Hope this helps!

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now