Product Catalog Schema design
- by FlySwat
I'm building a proof of concept schema for a product catalog to possibly replace a very aging and crufty one we use.
In our business, we sell both physical materials and services (one time and reoccurring charges).
The current catalog schema has each distinct category broken out into individual tables, while this is nicely normalized and performs well, it is fairly difficult to extend. Adding a new attribute to a particular product involves changing the table schema and backpopulating old data.
An idea I've been toying with has been something along the line of a base set of entity tables in 3rd normal form, these will contain the facts that are common among ALL products.
Then, I'd like to build an Attribute-Entity-Value schema that allows each entity type to be extended in a flexible way using just data and no schema changes.
Finally, I'd like to denormalize this data model into materialized views for each individual entity type. This views are what the application would access.
We also have many tables that contain business rules and compatibility rules. These would join against the base entity tables instead of the views.
My big concerns here are:
Performance - Attribute-Entity-Value schemas are flexible, but typically perform poorly, should I be concerned?
More Performance - Denormalizing using materialized views may have some risks, I'm not positive on this yet.
Complexity - While this schema is flexible and maintainable using just data, I worry that the complexity of the design might make future schema changes difficult.
For those who have designed product catalogs for large scale enterprises, am I going down the totally wrong path? Is there any good best practice schema design reading available for product catalogs?
