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
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),
ModelYear DECIMAL(5, 1) NOT NULL,
Seating TINYINT NOT NULL,
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.
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.
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.
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
Regions to allow for the "Ford Fusion" to be the same model as the "Ford Mondeo". Alternatively, just treat them as separate models.