• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3087
  • 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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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