1

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:

Database Designs for Cars

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?

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);
  • Moreover, create as well a unique index for MAKE table: CREATE UNIQUE INDEX make_idx ON make (name ASC); – AMG Aug 15 '18 at 14:42
  • Regarding the YEAR. I also believe that the YEAR is associated to the MODEL and not to the TRIM.... If you think it deeper, a Toyota Prius 2012 is way too different to a Toyota Prius 2018.... so yes, I would put the year as an attribute of the model and not the TRIM... but keep in mind that the important thing is that you create the proper UNIQUE constrainsts or indexes to avoid repeated and undesired data. – AMG Aug 15 '18 at 14:54
  • If you're correct about the year being associated to the model and not to the trim, then you suggest I move the year column to the model table, right? Keep everything else the same? I still feel that year is associated with trim (e.g. 2012 Toyota Prius A, 2012 Toyota Prius B, 2012 Toyota Prius C -- then A, B, C trims no longer exist in 2013 onwards)? – DatabaseNewbie Aug 15 '18 at 15:33
  • I think this site probably also thinks year is associated with trim - car2db.com – DatabaseNewbie Aug 15 '18 at 15:41
  • Yes, that is exactly what I'm saying... A, B, C will no longer exist in 2013 Toyota PRIUS (at least not the same A,B,C that you had in 2012, they can be totally different). Keep in mind that it doesn't matter where you place the YEAR atribute... It can be part of either table (MODEL or TRIM). Use it according to what you have planned on the app solution (programation) and whatever it makes you feel confortable. What really matters is that you only have one and unique record of TOYOTA PRIUS 2012 A. – AMG Aug 15 '18 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.