Referencing multiple tables from one field

mtntrip

I am working on creating a product catalog database, and have been stumped on a single problem for a very long time!

This is a simplified sample of my current design:

Product
- Part Number
- Price

Product Application (Relation)
- Range From Year
- Range To Year
- Product ID
- Car ID
- SUV ID
- Truck ID
- Van ID

Truck
- Make
- Model ID
- Exterior Door (actually related to Cab)
- Exterior Cab
- Exterior Box

Car, SUV, etc.

The problem I have is mainly caused by the fact that a Truck has additional characteristics (Box, Cab) than a Car, SUV, and Van. If I was just modelling a Truck, I would be doing great.

The above model is one solution I've thought of... but I'm not too satisfied. Although it allows a Product ID to relate to a Car, SUV, Truck, and Van ID... that very rarely happens, so I would end up with alot of unused fields.

For example:

TRUCKPART1 - CarID 0, SUVID 0, Truck ID 1, Van ID 0
TRUCKPART2 - CarID 0, SUVID 0, Truck ID 1, Van ID 0
TRUCKPART3 - CarID 0, SUVID 0, Truck ID 2, Van ID 0
TRUCKPART4 - CarID 0, SUVID 0, Truck ID 1, Van ID 0
TRUCKPART5 - CarID 0, SUVID 0, Truck ID 4, Van ID 0
etc...

Is this kinda waste acceptable? It just seems like there has to be a better way?

The other way I was thinking of, was to have the Product Application (Relation) table look like:

Product Application (Relation)
- Range From Year
- Range To Year
- Product ID
- Vehicle ID
- Vehicle Table

Where Vehicle Table would tell what table the Vehicle ID references. Although, this is kinda hackish, and who knows how well it would work.

The reason I don't have just one Vehicle Table, rather than a separate one for each type (Car, SUV, Truck, Van), is that for every Product that wasn't for a Truck, the fields that related only to a Truck would be unused (I did think of just having a "Not Applicable" value... but that also doesn't seem right).

I hope I've made some sense. 🙂

PHPdev

I think what you are looking to do is NORMALIZE your design...

My sudgestion is to have one table that has all the comonalities for a vehicle... Like Make Model id in one table. Then in that same table have a TYPE fileld that relates to the TYPE table.

Your type table has two fileds ID, and Type

ID TYPE
1 TRUCK
2 CAR
3 SUV
etc...

Then you would have other tables one for each type, which holds special characteristics of each type..

This way you only have a vehicle ID number on your parts table... Now this all depends that you have a M-1 relationship between your parts and your vehicles... Meaning that every part only fits one vehicle but a vehicle can have many parts.

If one part can fit many vehicles... I would say then you need another table to relate the parts to vehicles. this table would hold part_id and vehicle_id

Hope this all makes sense...

PHPdev