title: Product Catalog Schema design
body: <p>I'm building a proof of concept schema for a product catalog to possibly replace a very aging and crufty one we use.</p>
  
  <p>In our business, we sell both physical materials and services (one time and reoccurring charges).</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Then, I'd like to build an Entity-Attribute-Value schema that allows each entity type to be extended in a flexible way using just data and no schema changes.</p>
  
  <p>Finally, I'd like to denormalize this data model into materialized views for each individual entity type. These views are what the application would access.</p>
  
  <p>We also have many tables that contain business rules and compatibility rules. These would join against the base entity tables instead of the views.</p>
  
  <p>My big concerns here are:</p>
  
  <ul>
  <li>Performance - Attribute-Entity-Value schemas are flexible, but typically perform poorly, should I be concerned?</li>
  <li>More Performance - Denormalizing using materialized views may have some risks, I'm not positive on this yet.</li>
  <li>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.</li>
  </ul>
  
  <p>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?</p>