How to model a car (year, make, model, trim)?

Question

I'm trying to design a database to represent cars (specifically: year, make, model, trim) for a car dealership application. Based on my understanding of the data, I've come up with the two database designs below:

Example data:

Year:  2013
Make:  Nissan
Model: Murano
Trim:  SL

I think the top design is more correct than the bottom design - but I'm not 100% sure.

The plan is that for every vehicle added into the car dealership's inventory, I would assign that inventory item a trim_id (which would have details about the vehicle's standard features, options, etc.). The inventory item would then just need to select which options apply to it, specify odometer reading, condition, price, etc.

The model would also need to be able to support queries like:

• List all make for a particular year
• List all model for a particular make
• List all trim for a particular model

Is the design in the right direction? Normalized?

Database Design Cars-Models / Car Classifieds - this puts the year column with the model. I'm not a car buff, but I don't think this is right. I think the trim is associated with the year?

Answer 1

The first model is more accurate for your business needs. I would go a little bit further and include an index (unique) that includes NAME and YEAR. If you keep the model as it is; for example, the model doesn't prevent you to have this data:

TRIM:
id     model_id     name     year
==================================
1      1            SL       2013
2      1            SL       2013
3      1            SL       2013

To avoid this situation create one index in TRIM with this:

CREATE UNIQUE INDEX trim_idx ON trim (model_id ASC,name ASC,year ASC);

And do a similar action for MODEL

CREATE UNIQUE INDEX model_idx ON model (make_id ASC,name ASC);