3

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

  • The design you created on sqlfiddle is referred to as Entity-Attribute-Value or EAV. You may wish to search for that term to learn more about the pros and cons of that kind of schema. – Max Vernon Sep 29 '15 at 3:51
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.