0

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?
1

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

  • You might be right about the start date concept. I think I can address that by changing my year column to start_date. I'm still trying to understand your SubModels and SubModelTrims concepts. I think you solved my problem with 6 tables, which I did in only 5 tables? – DatabaseNewbie Apr 21 at 7:38
  • 1
    Also - why natural keys? What's the benefit? I need to display information about these vehicles on a website. I was planning to use the PKs in the URL. – DatabaseNewbie Apr 21 at 7:41
  • 1
    1. Your 5 tables structure will allow for body types that don't necessarily exist for that model. For example, you can have a trim for a Corolla hatchback even if one doesn't exists. The sub-models define which body types each model actually has, and only then ties it to the trim level. 2. The benefits of using natural keys are numerous, You can watch my video on the topic here: youtube.com/watch?v=r2MDDPDi_Pg . In your case, the above model will strongly enforce consistency of the data. – SQLRaptor Apr 21 at 18:33
  • 1
    Well, you can also enter a fictional manufacturer as well, the database can’t protect you from that. The advantage of the sub model is that it is reference data from outer sources, in your case the manufacturer. The manufacturer provides a list of models and body types. These available sub models are independent of the trim level as they are in your model. How would you store the fact that there is a Toyota Corolla hatchback if you don’t yet know the trim level? My suggested design follows reality more accurately IMHO, although you could make it work either way. – SQLRaptor Apr 22 at 15:48
  • 1
    Also, to realize the benefits of natural keys I highly recommend that you just try it for yourself. Create both schemas, put some sample data in it, and try to think of some queries that will be used by your application. Both operational and reporting, and see for yourself what happens with natural keys... – SQLRaptor Apr 22 at 15:59
1

The automotive industry has plenty of exceptions, complicating such a project. For example, the Ford Escort ZX2 was marketed as a trimline of the Escort for the first couple of years, then stood alone as just "Ford ZX2" (with bumper emboss to match) starting in MY1999. So was it a submodel, or a standalone model, which happened to be named in reference to another model? Did it go from one to the other in 1998?

Rather than trying to fit the real world into a tidy but rigid taxonomy, I encourage you to accept that auto manufacturers are unpredictable. Keep some lookup tables, but let most of your logic live at the atomic level; here, I've called that Lines:

CREATE TABLE Manufacturers
(
ManufacturerID  SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ParentManufacturerID    SMALLINT REFERENCES Manufacturers (ManufacturerID),
ManufacturerName    VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Models
(
ModelID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ModelName   VARCHAR(100) NOT NULL,
CombinedName    VARCHAR(100) NOT NULL UNIQUE  -- Generally, the manufacturer name || ' ' || model name
);

CREATE TABLE BodyStyles
(
BodyStyleID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
BodyStyleName   VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE `Lines`
(
LineID  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-- Foreign keys
ManufacturerID  SMALLINT NOT NULL REFERENCES Manufacturers (ManufacturerID),
ModelID INT NOT NULL REFERENCES Models (ModelID),
BodyStyleID SMALLINT NOT NULL REFERENCES BodyStyles (BodyStyleID),
-- Attributes
ModelYear   DECIMAL(5, 1) NOT NULL,
PowerSystem VARCHAR(30),
Seating TINYINT NOT NULL,
SubModelName    VARCHAR(100),
TrimLineName    VARCHAR(100),
UNIQUE (ManufacturerID, ModelID, BodyStyleID, ModelYear, PowerSystem, Seating, SubModelName, TrimLineName)
);

This gives you the flexibility for a car to exist with multiple body styles, while limiting the trim lines available to each. E.g., perhaps the Mustang coupe is available in GX, LX, and GT trim lines, while the convertible is only available in LX and GT.

You could use Manufacturers.ParentManufacturerID for arms-reach ownership like Tata of Land Rover, or only use it for tighter relationships like Ram to Dodge. This is a slowly-changing dimension, so if you want to be able to report historically, you'll need to add a DateEffective field, and include it in the primary key.

The Models.CombinedName field lets you handle cars with odd names: whereas for most cars you can concatenate the make and model ("Ford Mustang"), for a few the model name includes the manufacturer name. The Mazda6 is manufactured by Mazda, but the model name is "Mazda6", not "6", so you should store that, but you certainly wouldn't display it as the "Mazda Mazda6".

Lines.ModelYear is a DECIMAL to allow for half-year changes or additions, such as the 1964.5 Mustang. Be sure to translate that the "1964 ½" in the display layer. You could store text values rather than numbers - but sooner or later you'll get both "1964½" and "1964 ½" and "1964 1/2" in that field, unless you vet your inputs carefully.

The Lines.SubModelName field lets you handle cases like the Camry Solara, Escort ZX2, and Yukon Denali. You could make the case that "Denali" is a GM sub-brand like Buick, Chevrolet, or GMC, but I think sub-model is appropriate.

The Lines.PowerSystem field exists because some models are distinguished more by their engine as their trim level, and some trim lines are only available with some engines. 30 characters is a middle ground between concision and description. If all you want to record is the geometry and number of cylinders (e.g., I4, V12), then three characters may be enough; if you want lots of details on each engine, you should make that another table and store a reference instead. I recommend PowerSystem rather than Engine, to allow for hybrids and electric vehicles.

Some cars are available with different seating arrangements, such as mid-size SUVs sold with or without a third row, or minivans with benches or chairs in the middle row, so if you want Seating as an attribute, it needs to be part of the unique constraint (or just record a single value for "default number of seats").

Many cars are sold under different names in different regions. You may want to add a Regions table and a many-to-many table between Models and Regions to allow for the "Ford Fusion" to be the same model as the "Ford Mondeo". Alternatively, just treat them as separate models.

0

Q1: "correct" in what sense - according to theory or practice or performance?

Q2: JOINs are not that deadly.

Q3: We need to see the SELECTs that you will use before we can judge your Schema.

Q4: You are obsessing about things that are not that important.

The textbook discusses one reason for normalizing. Real life has 2 reasons:

  • To centralize the modification of something that could change. However... Once you have entered a car make/model/etc, it won't change. Hence, this is only a weak reason for normalizing.

  • Performance -- An unnormalized schema is bulky. Bulky is slow if it cannot live entirely in RAM. However... The Car schema will easily fit in today's RAM sizes. Hence, this is only a weak reason for normalizing.

Advice:

  1. Build something.
  2. Play with it.
  3. Tweak it -- or start over. This step is where you get your real lesson in schema design.

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.