What's the best way to store and search through many product specifications

Question

Let's say I have a lot of products and all of those products have a lot of specifications. These specifications can change at any time (e.g. "colour" could suddenly be available for new products) and some products do not have certain specifications.

1. What would be the best way to store this?
2. What would be the best way to search through this?

In the end, there will be tens of thousands of products each with dozens of specifications. Adding a column for each specifications feels wrong, so I'm assuming having a separate table for the specifications and then mapping them through a third table is the best way to go.

How would I be able to search through these specifications easily while maintaining performance when the products_specs table grows to millions of records?

Here's the schema and query I came up with: http://sqlfiddle.com/#!9/53f49/3

Answer 1

One way is to design the specifications in a supertable/subtable form. Here is one such design: Fiddle. I'm sure it can be improved on, but the key point is that specification ID values are unique among all specifications, not just within each type of specification. So there are no collisions between Colour and Weight, for example. However, a spec defined as colour cannot be entered into any other spec subtable except Colors (or Colours -- try as I might, I'm afraid I mixed the spelling in at least one location). The super table (Specs) makes it easy to query individual specs.