Vehicle data table I am normalizing:
ABI_CODE INT <Unique Identifier
MOTOR_TYPE VARCHAR <Car, Bike or Van
MAKE VARCHAR <Renault, Ford etc.
MODEL VARCHAR <Focus, Fiesta etc.
MANUFACTURED_FROM INT <start year
MANUFACTURED_TO INT <end year
ENGINE_CC INT <999, 1199 etc.
ENGINE_TYPE VARCHAR <Diesel, Petrol or Electric
TRANSMISSION VARCHAR <Manual or Automatic
Where I am so far:
Type ( MOTOR_TYPE )
Make ( MAKE )
Model ( ABI_CODE, MODEL, MANUFACTURED_FROM, MANUFACTURED_TO, ENGINE_CC )
Engine_Type ( ENGINE_TYPE )
Transmission ( TRANSMISSION )
One Make can have many Models.
One Model can have one Motor_type, Engine_Type, Manufactured_From, Manufactured_To, Engine_CC and Transmission.
First I would everywhere add an unique identifier, to properly make the specific relations.
At last, your data model would look like this:
Model ( MODEL_ID (PK), ABI_CODE, MODEL, MANUFACTURER_ID (FK),
ENGINE_ID (FK), MOTOR_TYPE, Transmission, MAKE)
ManuFacturer (MANUFACTURER_ID, MANUFACTURED_FROM, MANUFACTURED_TO)
Engine ( ENGINE_ID, ENGINE_CC, ENGINE_Type )
Depending on how you think your data schema will change, you can either have an own table for motor-type, make and transmission or not. At the moment, I would recommend against having their own tables, because it would simply result in a table with one PK and one value.
The Manufacturer's and Engine's information, though, are more suitable in their own table.