Mysql – Database Design for Cars

database-designMySQLnormalization

I'm designing a database to capture car data. Here's a simplified design for capturing year, make, model, and trim:

Design 1

This should handle data like this:

2017 Ford Mustang GT

However, it turns out that the GT trim can come in two body types: Coupe and Convertible. So, the data now looks like this:

2017 Ford Mustang GT (Coupe)
2017 Ford Mustang GT (Convertible)

In order to accommodate the body type, I added a FK in the trims table that references the body_types table:

Design 2

So, whenever I need to reference a specific car, I do so via trims.id, which gives me the makes.name, models.name, trims.name, trims.year and the associated body type. This second design is prone to anomalies if I ever need to update trims.name, however.

In order to combat that, I added a trim_body_types table:

Design 3

Now, if I want to refer to a specific car, I do so via trim_body_types.id. I think this 3rd design is probably normalized, but will also probably cost me in JOINs.

Assume that the appropriate UNIQUE constraints are in place.

QUESTIONS

  1. Is my 3rd design the correct normalized design?
  2. If so, should I accept the denormalized 2nd design instead in order to save on JOINs?
  3. Any potential pitfalls with the designs I'm considering (specifically Design 2 and Design 3)?
  4. Or am I completely off and need to consider a different solution?

Best Answer

I think trims can change through the year so you need a specific date start for a trim. Model + body types is considered a 'sub model' AFAIK, and is not determined by 'trims-to-body types'. A Toyota Corolla Sedan may have different trims than the Coupe. Also, how about you try this with natural keys and see what happens:

Manufacturers = (Manufacturer VARCHAR(20) PRIMARY KEY);

BodyTypes = (BodyType VARCHAR(10) PRIMARY KEY);

Models = (Manufacturer VARCHAR(10) NOT NULL REFERENCES Manufacturers(Manufacturer), Model VARCHAR(10) NOT NULL, PRIMARY KEY (Manufacturer, Model);

SubModels = (Manufacturer VARCHAR(10) NOT NULL, Model VARCHAR(10) NOT NULL, BodyType VARCHAR(10) NOT NULL REFERENCES BodyTypes (BodyType), FOREIGN KEY (Manufacturer, Model) REFERENCES Models(Manufacturer, Model), PRIMARY KEY(Manufacturer, Model, BodyType));

Trims = (Trim VARCHAR(10) PRIMARY KEY);

SubModelTrims = (Manufacturer VARCHAR(10) NOT NULL, Model VARCHAR(10) NOT NULL, BodyType VARCHAR(10) NOT NULL, Trim VARCHAR(10) NOT NULL REFERENCES Trims(Trim), SubModelTrimStartDate DATE NOT NULL, PRIMARY KEY(Manufacturer, Model, BodyType, Trim, TrimStartDate), FOREIGN KEY (Manufacturer, Model, BodyType) REFERENCES SubModels (Manufacturer, Model, BodyType));

What joins do you need now?

HTH

Related Question