Automobile table design

[Jack]

I am creating a db for a private investigator and am having difficulties
figuring out how to design the automobile section.

I have created tables with the following:
tblCarBrand
pk CarBrandID
CarBrandName

tblCarType
pk CarTypeID
CarType (Truck or Car)

tblCarMake
pk CarMake
fkCarBrandID
fkCarTypeID

tblCarColor
pkCarColorID
CarColor

tblCarYear
pkCarYear
CarYear




My goal is to create a form with combo boxes that will allow him to use
combo box to first select type (car or truck), then select Car Brand, then
Car Make.

I would like to get suggestions whether or not my structure is correct. Or
would it be better for me to create one large table with all of the fields
above. This information will then be transferred to another table -
tblWitness. For example he keys in witness#1 info(name,etc). When he gets to
automobile section, I want the info from combo boxes to be stored in witness
table.

Thanks for your help.
Jack

 

[mscertified]

There a lot of combinations to consider, I'd have
BRAND eg. BUICK
MODEL e.g. REGAL
SUB-MODEL eg. LIMITED
TYPE eg SEDAN, CONVERTIBLE
YEAR eg 2000
DOORS eg 2 or 4
COLOR eg. RED (need to allow multiple choises)
WHEELS eg. CHROME OR ALUMINUM
etc. etc.

Main table will be VEHICLE
attributes of vehicle will be here e.g. type, year, color etc.

BRAND, MODEL and SUB-MODEL will be in a single hierarchy table like:
Key
Description
ParentKey

Keeping a database like this current will be a full-time job.

-Dorian