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.
