I've read a bunch of threads/posts before joining and perhaps I've read so much I can't see the wood for the trees anymore or maybe I just don't get it..

My problem is an old one. Products - attributes - values.

I'm no a database pro, but have read enough to figure out EAV is something I should avoid if I can. I'm just not 100% on what I should do.

I'm lucky in that my attributes are dead simple, size and colour (well for now..).

• One product table with defined attribute fields is out.
• I did EAV and whilst it works I'm trying to do better.
• I did a specific size and colour table with a product_id FK. Works fine and is simple. I can't say requirements won't change in the future to include say diameter though.
• I did product types, with attributes per type which is pretty good

I've ended up with the attached.

I think it will allow me to have a product with variations depending on their selected options and tie a specific price and quantity to that variant.

I'd appreciate any feedback.

Is there any reason why I shouldn't merge "products_pricing" with "products_variants"?

I 'built' the above and it threw up some problems.

Namely, the relationship: products -> products_options wouldn't allow an option to exist without a product which I suppose makes sense but in (my) real world I'd need to keep "un applied" options.

I updated it to: