Database schema for Product Properties

Question

As so many people I'm looking for a Products /Product Properties database schema. I'm using Ruby on Rails and (Thinking) Sphinx for faceted searches.

Requirements:

• Adding new product types and their options should not require a change to the database schema
• Support faceted searches using Sphinx.

Solutions I've come across:
(See Bill Karwin's answer)

Option 1: Single Table Inheritance

Not an option really. The table would contain way to many columns.

Option 2: Class Table Inheritance

Ruby on Rails caches the database schema on start-up which means a restart whenever a new type of product is introduced. If you have a size able product catalog this could mean hundreds of tables.

Option 3: Serialized LOB

Kills being able to do faceted searches without heavy application logic.

Option 4: Entity-Attribute-Value

For testing purposes, EAV worked fine. However it could quickly become a mess and a maintenance hell as you add more and more options (e.g. when an option increase the prices or delivery time).

---

What option should I go with? What other solutions are out there? Is there a silver bullet (ha) I overlooked?

Answer 1

IMO, a combination of a class table inheritance and EAV with some serious restrictions would be the best approach.

EAVs are like drugs: in small quantities and a narrow set of circumstances, they can be benefical; too much will kill you. EAVs as the main portion of the schema will fail. No question. However, as a supplement to a good database schema they can be useful if you enforce proper restrictions.

The main rule with an EAV is that you can never write a query that includes '[AttributeCol] = 'Attribute'. In other words, you can never filter, sort, restrict in range nor place a specific attribute anywhere on a report or form. It is just a bag of data that can be spit out entirely on a report or onscreen in a list. In fact, I have seen people implement this feature as an Xml column.

If you are able to maintain this restriction, then you can add an EAV structure to a class table inheritance design as a means of allowing users to add a set of attributes to a product merely for storage purposes. The moment they want to do any of the verboten tasks with an attribute, it must become a first class column and all that entails.

The key is in enforcement. If you do not feel that you can reasonably enforce or have other developers enforce this limitation on the use of the EAV structure, then it might make sense to skip it. However, if you can enforce this limitation, it can solve the problem of people wanting to add tons of attributes merely for storage and tracking purposes.