This design problem has been an ongoing problem for me for a long time.

What I'm trying to model is the products our company sells, and the cars, trucks, etc. that those products fit.

So in my current design I have these tables:

- application
- model
- model_exterior
- product

The 'application' table contains the 'product_id', 'model_id', 'model_exterior_id'. So 'application' is a junction entity.

The problem I'm faced with right now is that some products have additional, unrelated attributes that the other products don't. The thing that would bother me about this current design, is that I'd have a bunch of attributes in the 'product' table that would only be used by one type of product. This would result in a lot of NULL's or unused/unrelated fields for the rest of the products.

The only thing I can think of, would be to create a table for each product category. However, if I did that, I'm not quite sure how to make it work properly, other than adding another ID to 'application' for each additional product category table.

For example:


- application
- model
- model_exterior
- product_rpk
- product_rb
- product_bc

Table: application

- product_rpk_id
- product_rb_id
- product_bc_id
- range_year1
- range_year2
- model_id
- model_exterior_id

I guess that might work, because in theory, a particular vehicle or model, could have multiple products on it at once... hmm...