title: Define Generic Data Model for Custom Product Types
body: <p>I want to create a product catalog that allows for intricate details on each of the product types in the catalog.  The product types have vastly different data associated with them; some with only generic data, some with a few extra fields of data, some with many fields that are specific to that product type.  I need to easily add new product types to the system and respect their configuration, and I'd love tips on how to design the data model for these products as well as how to handle persistence and retrieval.</p>
  
  <p>Some products will be very generic and I plan to use a common UI for editing those products.  The products that have extensible configuration associated with them will get new views (and controllers) created for their editing.  I expect all custom products to have their own model defined but to share a common base class.  The base class would represent the generic product that has no custom fields.</p>
  
  <p>Example products that need to be handled:</p>
  
  <ol>
  <li>Generic product
  
  <ul>
  <li>Description</li>
  </ul></li>
  <li>Light Bulb
  
  <ul>
  <li>Description</li>
  <li>Type (with an enum of florescent, incandescent, halogen, led)</li>
  <li>Wattage</li>
  <li>Style (enum of flood, spot, etc.)</li>
  </ul></li>
  <li>Refrigerator
  
  <ul>
  <li>Description</li>
  <li>Make</li>
  <li>Model</li>
  <li>Style (with an enum in the domain model)</li>
  <li>Water Filter information
  
  <ul>
  <li>Part number</li>
  <li>Description</li>
  </ul></li>
  </ul></li>
  </ol>
  
  <p>I expect to use MEF for discovering what product types are available in the system.  I plan to create assemblies that contain product type models, views, and controllers, drop those assemblies into the bin, and have the application discover the new product types, and show them in the navigation.</p>
  
  <ol>
  <li><p>Using SQL Server 2008, what would be the best way to store products of these various types, allowing for new types to be added without having to grow the database schema?</p></li>
  <li><p>When retrieving data from the database, what's the best way to translate these polymorphic entities into their correct domain models?</p></li>
  </ol>
  
  <hr>
  
  <h2>Updates and Clarifications</h2>
  
  <ol>
  <li><p>To avoid the Inner Platform Effect, if there is a database table for every product type (to store the products of that type), then I still need a way to retrieve all products that spans product types.  How would that be achieved?</p></li>
  <li><p>I talked with Nikhilk in more detail about his SharePoint reference.  Specifically, he was talking about this: <a href="http://msdn.microsoft.com/en-us/library/ms998711.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms998711.aspx</a>.  It actually seems pretty attractive.  No need to parse XML; and there is some indexing that could be done allowing for simple and fast queries over the data.  For instance, I could say "find all 75-watt light bulbs" by knowing that the first int column in the row is the wattage when the row represents a light bulb.  Something (NHibernate?) in the app tier would define the mapping from the product type to the userdata schema.</p></li>
  <li><p>Voted down the schema that has the Property Table because this could lead to lots of rows per product.  This could lead to index difficulties, plus all queries would have to essentially pivot the data.</p></li>
  </ol>