MySQL vehicle database schema

Posted on 2007-08-05
Last Modified: 2013-12-12
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!
Question by:stevefNYC
    LVL 44

    Expert Comment

    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.

    LVL 2

    Accepted Solution

    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
     - year (pk)
     - name
     - rrp
     - ...

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

    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, FROM makes JOIN models ON make_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 = 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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now