Database design question. I am close ;)

Lets say I am selling parts for cars, trucks, suvs, vans, etc.

I want a user to search for a part by selecting a vehicle type, then a vehicle make, and then a vehicle model.

So

Vehicle Makes have to be related to Vehicle Types (I.E: Land Rover doesn't make a car) and vehicle models need to be related to vehicle make (I.E Toyato doesn't make a mustang Ford does).

So I am okay with those relationships individually but what is confusing me is how to ensure that a Mustang doesn't show up as a Ford Car.

Lets say I have:

vehicle_type
vehicle_type_cd
vehicle_type_desc

vehicle_make

vehicle_make_cd
vehicle_make_desc

vehicle_model
vehicle_model_cd
vehicle_model_desc
Then I might have an intersect between vehicle_type and vehicle_make because certain makes don't have certain types of vehicles. BMW doesn't have a truck, Lexus doesn't have a truck, so when a user selects truck as a make lexus nad BMW shouldn't show up in the drop down.
Vehicle_type_make
Vehicle_type_make_key
Vehicle_type_cd
Vehicle_make_cd

I might have the same relationship between make and model:

Vehicle_make_model
Vehicle_make_model_key
Vehicle_make_cd
Vehicle_model_cd

With this database design, if I were running a SQL statement to find criteria that matched "Truck" and "Ford" to populate the model dropdown I wouldn't know whether mustang was a truck or car.
What would be the best way to design this.

I hope you can help.

Thank you so much

Given the table structures you have provided, this query should return you the results you are looking for:

SELECT
*
FROM
Vehicle_Model VML
INNER JOIN
Vehicle_Make_Model VMM ON VML.Vehicle_Model_Cd = VMM.Model_Cd
INNER JOIN
Vehicle_Type_Make VTM ON VMM.Make_Cd = VTM.Vehicle_Make_Cd
INNER JOIN
Vehicle_Type VT on VTM.Vehicle_Type_Cd = VT.Vehicle_Type_Cd
INNER JOIN
Vehicle_Make VMK ON VMM.Vehicle_Make_Cd = VMK.Vehicle_Make_Cd
WHERE
VT.Vehicle_Type_Desc = 'Truck' AND
VMK.Vehicle_Make_Desc = 'Ford'

|||Woah. I wasn't thinking to clearly was I? Thanks for knocking some sense in to me.
Umm, okay what do you think about that design is it okay?|||This is the structure you've laid out:

vehicle_type
vehicle_type_cd
vehicle_type_desc

vehicle_make
vehicle_make_cd
vehicle_make_desc

vehicle_model
vehicle_model_cd
vehicle_model_desc
Vehicle_type_make
Vehicle_type_make_key
Vehicle_type_cd
Vehicle_make_cd

Vehicle_make_model
Vehicle_make_model_key
Vehicle_make_cd
Vehicle_model_cd

To me, that seems to complicated. Models are always going to betied to a Make, right? Even if there was a Ford Thunderbird and aHyundai Thunderbird, you'd want to treat them as their own separateentities. Both Thunderbirds might not necessarily be cars, soyour design falls apart. And the vehicle type is tied to thespecific model, not to the make, right? Maybe I am not looking atit closely enough, but I think you could probably simplify it like this:

vehicle_type
vehicle_type_cd
vehicle_type_desc

vehicle_make
vehicle_make_cd
vehicle_make_desc

Vehicle_make_model
Vehicle_make_model_key
Vehicle_make_cd
Vehicle_type_cd
Vehicle_model_desc

|||Perfect. I am convinced. Make writing the procs a lot easier as well. :)
|||

Another question already.

Okay lets say that I am actually selling vehicles.

So if someone is listing a truck I want to capture different things than if they are selling a car.
For example I want to capture towing capacity if they are selling a truck but not a car. On the other hand I want to capture mileage whether its a car or a truck.
How would you do the table layout for something like that?

|||Well, one way you could do it would be like this:

attributes
attribute_cd
attribute_desc

vehicle_type_attrbitutes
vehicle_type_cd
attribute_cd

The attributes table would have entries like "towing capacity" and"mileage". The vehicle_type_attributes table would have onerecord for every attribute applicable to the specified vehicle_type.
|||

But then how would I store that data in the item table:

So lets say I have a table
item_details
item_id
vehicle_type
Vehicle_make
Vehicle_model
Vehicle_year
Vehicle_mileage
Vehicle_description
Now I don't want to add the value vehicle_length becuase that is only applicable if the type of vehicle is a boat. So Where do I store that?
item_boat_details?
And then only check that if its a boat? Then I would need conditional SQL which seems silly?
Thanks

|||Well, then you could have:

attributes
attribute_id
attribute_desc

vehicle_type_attrbitutes
vehicle_type_attributes_id
vehicle_type_id
attribute_id

item_details
item_id
vehicle_type_id
vehicle_make_id
vehicle_model_id

item_attributes
item_attributes_id
item_id
attribute_id
item_attribute_value

Year, mileage, length, and description could be attributes, and theirvalues for the specific vehicle would be found in the item_attributestable.
And yeah, I know, I've been all over the place with the keys for thesetables. My personal preference (and you will find many validarguments against it), is a single surrogate key for each record, and Iuse "id" for these. Those table structures above more closelymatch how I'd structure it (well, except for the underscores, which Idislike).
|||

Okay. So I am kind of scared of dynamic attributes.

What about having a table for suv_details, and a table for car_details, and a table for truck_detaiis. Etc.

|||Sorry, I don't think that is a good idea.