how to attach vehicles to my products in my database schema

I made a basic vehicles year/make/model/submodel database diagram here. I'm having a hard time wrapping my 开发者_Python百科head around how I need to further set up my database so products know vehicles they belong to.

I'm thinking that I need to make another table that serves as a many to many relationship between Products and VehicleYears.

Essentially I want to be able to choose a vehicle on the front end and only show products that fit the currently selected vehicle.

This then brings up another possible issue. If vehicle years has a many relationship to products, would I still be able to filter out categories and/or sub categories based on the currently selected vehicle? (products belong to sub categories which then belong to categories)

I'm sure there is a way to write a query to handle categories and sub categories, but is there a better way to construct the database so that performance isn't an issue? My categories/item diagram is here.

• After getting feedback here is my revised design here

Yes, theoretically you need an intermediate table from VehicleYears to Products.

But I don't understand your VehicleYears and VehicleYears_VehicleMakes tables. What does the VehicleYears_VehicleMakes relationship mean? That the vendor made any cars in that year? I think the relationship is rather a submodel-to-year one and if it is, you don't even need a table for your years, just put two columns into the submodel table.

Nick, the first thing that hits me is that you need to forget about modelling year as a separate table. So remove VehicleYears and VehicleYears_VehicleMakes. You just need add a year field to your VehicleSubModels table. I think year is better tied to the submodel of vehicle rather than a make of vehicle.

I think the link you are struggling to find between vehicles and products is a many:many between VehiclSubmodels and Items. Your new table VehicleProducts would have two fields ( a compound primary key) comprising of VehicleSubModelId and ItemId.

Modelling it this way you have a very granular way to relate products with vehicles. And you could easily achieve your goal of choosing a vehicle and getting all the relevant items listed.

Your design for categories and sub categories is fine but I wonder if you'll ever get to a point where you need a third level of categorisation - a SubSubCategory :) if so it may be wise to bite the bullet now and get that in your design.

I've done something like this for publisher trying to attach repair procedures to a product. The product is identified by a four-part primary key: Year, Make, Model, Engine, or YMME, and the vehicles table looked like this (simplified):

VEHICLES
year  integer
make   varchar
model  varchar
engine varchar

The PARTS table has to be linked to an entity in the VEHICLES table. A part might work with a specific year and make and model, but not when a specific engine was installed.

In your case, you have to decide what constellation of attributes uniquely identifies a bicycle. If you're tracking bikes from multiple makers, you need MAKE. If things change from year to year, you need YEAR. If there is a difference from model to model, you need MODEL. If, say, a part will work if the frame is aluminum but not if the frame is steel, you'd need FRAME_MATERIAL or FRAME_GAUGE as well, in place of ENGINE, or you'd need a separate model one for aluminum, the other for steel.

VEHICLES
year  
make 
model 
frame

Then you could simply have an intermediary table:

VEHICLE_PARTS
partid  integer foreign key references PARTS
year
make
model
frame
...

or

VEHICLE_PARTS
partid  integer foreign key references PARTS
year
make
model   (frame material is handled by a separate model)

Your structure has to answer the question: Will this part work with that bike? The mere presence of the partid in the VEHICLE_PARTS table should imply, Yes it does. You really want to avoid having an Exceptions field in the VEHICLE_PARTS table where there is some human-readable notation that says the part is incompatible with the bike in question when rams-horn handlebars are used. You should really create a separate model to handle things like that.