Database schema for Product Properties
- by Chemosh
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?
