I am building an extensible application that can handle any kind of product data.  Back in February, I started looking into a Generic Data Model for Custom Product Types.  After getting some feedback from my blog, on StackOverflow, and having some conversations with a few people, here’s the schema that I ended up with.

 DB Schema

Ugly.  Yes.  But this lets me store a single product record, such as “Light bulb,” but have many differently configured light bulbs in the product details table, such as a 45-watt halogen flood light.  The Product schema can be extended to include other fields that are shared by all products, and the ProductDetail schema will support any type of product.  Notice the XML field at the very end of the table that can be used to store anything that doesn’t neatly fit into the predefined columns.

The benefits that I see with this schema are:

  1. Simplicity (It’s flat and easy to understand)
  2. Indexing (I have created indexes for the first 2 columns of each data type)
  3. Extensibility (There’s only 1 table that can store any type of product)

Now that I have the data stored, I’m on to another problem.  How do I translate the generically-stored products into instances of specific products?  My goal is to create an extensible application where I can drop in assemblies that contain different classes for specific products, deriving from a base Product class.  Let me show you what I mean.  First, let’s define the Product class:

   1: public class Product
   2: {
   3:     public string ProductName { get; set; }
   4:  
   5:     protected internal bool? bit0 { get; set; }
   6:     protected internal bool? bit1 { get; set; }
   7:     protected internal bool? bit2 { get; set; }
   8:     protected internal bool? bit3 { get; set; }
   9:     protected internal bool? bit4 { get; set; }
  10:  
  11:     protected internal byte? tinyint0 { get; set; }
  12:     protected internal byte? tinyint1 { get; set; }
  13:     protected internal byte? tinyint2 { get; set; }
  14:     protected internal byte? tinyint3 { get; set; }
  15:     protected internal byte? tinyint4 { get; set; }
  16:  
  17:     protected internal int? int0 { get; set; }
  18:     protected internal int? int1 { get; set; }
  19:     protected internal int? int2 { get; set; }
  20:     protected internal int? int3 { get; set; }
  21:     protected internal int? int4 { get; set; }
  22:  
  23:     protected internal double? float0 { get; set; }
  24:     protected internal double? float1 { get; set; }
  25:     protected internal double? float2 { get; set; }
  26:     protected internal double? float3 { get; set; }
  27:     protected internal double? float4 { get; set; }
  28:  
  29:     protected internal DateTime? datetime0 { get; set; }
  30:     protected internal DateTime? datetime1 { get; set; }
  31:     protected internal DateTime? datetime2 { get; set; }
  32:     protected internal DateTime? datetime3 { get; set; }
  33:     protected internal DateTime? datetime4 { get; set; }
  34:  
  35:     protected internal string string0 { get; set; }
  36:     protected internal string string1 { get; set; }
  37:     protected internal string string2 { get; set; }
  38:     protected internal string string3 { get; set; }
  39:     protected internal string string4 { get; set; }
  40:  
  41:     protected internal Guid? guid0 { get; set; }
  42:     protected internal Guid? guid1 { get; set; }
  43:     protected internal Guid? guid2 { get; set; }
  44:     protected internal Guid? guid3 { get; set; }
  45:     protected internal Guid? guid4 { get; set; }
  46:  
  47:     protected internal decimal? money0 { get; set; }
  48:     protected internal decimal? money1 { get; set; }
  49:     protected internal decimal? money2 { get; set; }
  50:     protected internal decimal? money3 { get; set; }
  51:     protected internal decimal? money4 { get; set; }
  52:  
  53:     protected internal XElement xml { get; set; }
  54: }

This obviously maps to the database.  My Linq-to-SQL based repository can return instances of this Product class after projecting from the database.

   1: public IQueryable<Product> GetProducts()
   2: {
   3:     return from detail in this._context.ProductDetails
   4:            let product = detail.Product
   5:            select new Product
   6:            {
   7:                ProductName = product.ProductName,
   8:  
   9:                bit0 = detail.bit0,
  10:                bit1 = detail.bit1,
  11:                bit2 = detail.bit2,
  12:                bit3 = detail.bit3,
  13:                bit4 = detail.bit4,
  14:  
  15:                tinyint0 = detail.tinyint0,
  16:                tinyint1 = detail.tinyint1,
  17:                tinyint2 = detail.tinyint2,
  18:                tinyint3 = detail.tinyint3,
  19:                tinyint4 = detail.tinyint4,
  20:  
  21:                int0 = detail.int0,
  22:                int1 = detail.int1,
  23:                int2 = detail.int2,
  24:                int3 = detail.int3,
  25:                int4 = detail.int4,
  26:  
  27:                float0 = detail.float0,
  28:                float1 = detail.float1,
  29:                float2 = detail.float2,
  30:                float3 = detail.float3,
  31:                float4 = detail.float4,
  32:  
  33:                datetime0 = detail.datetime0,
  34:                datetime1 = detail.datetime1,
  35:                datetime2 = detail.datetime2,
  36:                datetime3 = detail.datetime3,
  37:                datetime4 = detail.datetime4,
  38:  
  39:                string0 = detail.string0,
  40:                string1 = detail.string1,
  41:                string2 = detail.string2,
  42:                string3 = detail.string3,
  43:                string4 = detail.string4,
  44:  
  45:                guid0 = detail.guid0,
  46:                guid1 = detail.guid1,
  47:                guid2 = detail.guid2,
  48:                guid3 = detail.guid3,
  49:                guid4 = detail.guid4,
  50:  
  51:                money0 = detail.money0,
  52:                money1 = detail.money1,
  53:                money2 = detail.money2,
  54:                money3 = detail.money3,
  55:                money4 = detail.money4,
  56:  
  57:                xml = detail.xml
  58:            };
  59: }


So my controllers can now get flattened Product instances that map to the 2-table Product|ProductDetail schema in the database.  But I certainly don’t want to be coding against bit1, float2, tinyint3.  Did you notice that I had those as protected?  Instead, I want to have specific classes for specific products when it’s known.  Let’s take for instance, a furnace filter.

   1: [Export(typeof(Product))]
   2: [ExportedProduct("Furnace Filter")]
   3: public sealed class FurnaceFilterProduct : Product
   4: {
   5:     /// <summary>
   6:     /// Gets the type of furnace filter
   7:     /// </summary>
   8:     /// <example>
   9:     /// Advaned Allergen
  10:     /// </example>
  11:     public string FilterType
  12:     {
  13:         get { return base.string0; }
  14:         set { base.string0 = value; }
  15:     }
  16:  
  17:     /// <summary>
  18:     /// Gets the width of this filter size
  19:     /// </summary>
  20:     public double? Width
  21:     {
  22:         get { return base.float0; }
  23:         set { base.float0 = value; }
  24:     }
  25:  
  26:     /// <summary>
  27:     /// Gets the height of this filter size
  28:     /// </summary>
  29:     public double? Height
  30:     {
  31:         get { return base.float1; }
  32:         set { base.float1 = value; }
  33:     }
  34:  
  35:     /// <summary>
  36:     /// Gets the thickness of this filter size
  37:     /// </summary>
  38:     public double? Thickness
  39:     {
  40:         get { return base.float2; }
  41:         set { base.float3 = value; }
  42:     }
  43:  
  44:     /// <summary>
  45:     /// Gets the unit of measure for this filter size
  46:     /// </summary>
  47:     public UnitOfMeasure UnitOfMeasure
  48:     {
  49:         get { return (UnitOfMeasure)base.int0; }
  50:         set { base.int0 = value.Value(); }
  51:     }
  52:  
  53:     public override string ToString()
  54:     {
  55:         return "{1:0.###}{0} x {2:0.###}{0} x {3:0.###}{0} {4}".FormatWith(this.UnitOfMeasure.Abbreviation(), this.Width, this.Height, this.Thickness, this.FilterType);
  56:     }
  57: }


Frankly, this probably seems pretty silly at this point.  You’re wondering why I want to have specific classes for products, but a generic schema.  Certainly I don’t want to create classes for every possible product when I refuse to expand the database schema!

Well, maybe I am crazy, but that is in fact what I’m trying to do.  In the application, users won’t be browsing for existing products, they’ll be doing data entry to create products.  Using the example from above again, imagine if Amazon.com opened its system up to allow any user to create furnace filters, entering the filter type and dimensions.  They would probably want a specific data entry screen for furnace filters rather than a series of textboxes.  For that, we could end up with a furnace filter Model, and a furnace filter View, but a generic Controller, and of course the generic repository and database.

I do plan to have a generic product model and a generic product view that can be used to enter products that don’t have a specific model and view defined in the system yet.  But then if that product becomes popular, I can whip up a new Model, a specialized View, and poof – I have an improved experience for the popular product, without having to change anything else in the system.  At least, that’s the goal.

For this extensibility, I want to be able to import new models very easily. Enter MEF.  If you look closely at my FurnaceFilterProduct class, you’ll see that I’m exporting it as a Product, with some custom metadata giving it a name.  This name matches up with the name in the Product table, while the rest of the class defines how the Model maps to the base Product class.  Since the generic properties were all protected, and the FurnaceFilterProduct properties are public, this yields a very friendly experience when working with the specific class.  Consumers don’t need to know what underlying properties the values are stored in, and it just feels like any other POCO.

For posterity, here’s the custom ProductNameAttribute metadata attribute:

   1: [MetadataAttribute]
   2: [AttributeUsage(AttributeTargets.Class, AllowMultiple = false)]
   3: public sealed class ProductNameAttribute : Attribute, IProductMetadata
   4: {
   5:     public ProductNameAttribute(string productName)
   6:     {
   7:         this.ProductName = productName;
   8:     }
   9:  
  10:     public string ProductName { get; private set; }
  11: }


With a MEF catalog set up, and my custom FurnaceFilterProduct class in the assembly, I can now discover the type.  But when a product is fetched from the repository, how do I consume it as a FurnaceFilterProduct rather than a base Product?  Let’s start with the test:

   1: [TestMethod]
   2: public void FurnaceFilter_Is_Available_From_ExtensibilityService()
   3: {
   4:     ExtensibilityService service = _catalog.ExtensibilityService;
   5:     TestProductRepository repository = new TestProductRepository();
   6:     Product baseProduct = repository.GetProducts().Where(p => p.ProductName == "Furnace Filter").SingleOrDefault();
   7:     Product furnaceFilterProduct = service.GetExtensibleProduct(baseProduct);
   8:     Assert.IsInstanceOfType(furnaceFilterProduct, typeof(FurnaceFilterProduct));
   9: }


I created ExtensibilityService.GetExtensibleProduct that will take a base product instance and return the extensible product instance – in this case, a furnace filter.

   1: [Export]
   2: public class ExtensibilityService
   3: {
   4:     [Import]
   5:     public ICollection<Export<Product, IProductMetadata>> ProductExports { get; private set; }
   6:  
   7:     public Product GetExtensibleProduct(Product baseProduct)
   8:     {
   9:         Product product = this.ProductExports
  10:             .Where(e => e.MetadataView.ProductName.Equals(baseProduct.ProductName, StringComparison.OrdinalIgnoreCase))
  11:             .First()
  12:             .GetExportedObject() as Product;
  13:  
  14:         product.ProductName = baseProduct.ProductName;
  15:  
  16:         product.bit0 = baseProduct.bit0;
  17:         product.bit1 = baseProduct.bit1;
  18:         product.bit2 = baseProduct.bit2;
  19:         product.bit3 = baseProduct.bit3;
  20:         product.bit4 = baseProduct.bit4;
  21:  
  22:         product.tinyint0 = baseProduct.tinyint0;
  23:         product.tinyint1 = baseProduct.tinyint1;
  24:         product.tinyint2 = baseProduct.tinyint2;
  25:         product.tinyint3 = baseProduct.tinyint3;
  26:         product.tinyint4 = baseProduct.tinyint4;
  27:  
  28:         product.int0 = baseProduct.int0;
  29:         product.int1 = baseProduct.int1;
  30:         product.int2 = baseProduct.int2;
  31:         product.int3 = baseProduct.int3;
  32:         product.int4 = baseProduct.int4;
  33:  
  34:         product.float0 = baseProduct.float0;
  35:         product.float1 = baseProduct.float1;
  36:         product.float2 = baseProduct.float2;
  37:         product.float3 = baseProduct.float3;
  38:         product.float4 = baseProduct.float4;
  39:  
  40:         product.datetime0 = baseProduct.datetime0;
  41:         product.datetime1 = baseProduct.datetime1;
  42:         product.datetime2 = baseProduct.datetime2;
  43:         product.datetime3 = baseProduct.datetime3;
  44:         product.datetime4 = baseProduct.datetime4;
  45:  
  46:         product.string0 = baseProduct.string0;
  47:         product.string1 = baseProduct.string1;
  48:         product.string2 = baseProduct.string2;
  49:         product.string3 = baseProduct.string3;
  50:         product.string4 = baseProduct.string4;
  51:  
  52:         product.guid0 = baseProduct.guid0;
  53:         product.guid1 = baseProduct.guid1;
  54:         product.guid2 = baseProduct.guid2;
  55:         product.guid3 = baseProduct.guid3;
  56:         product.guid4 = baseProduct.guid4;
  57:  
  58:         product.money0 = baseProduct.money0;
  59:         product.money1 = baseProduct.money1;
  60:         product.money2 = baseProduct.money2;
  61:         product.money3 = baseProduct.money3;
  62:         product.money4 = baseProduct.money4;
  63:  
  64:         product.xml = baseProduct.xml;
  65:  
  66:         return product;
  67:     }

This is where things are starting to feel wrong.  Should I be using composition instead of inheritance here?  Even if I was, what’s the right way layer this so that the views are working against specific types of products but the repository only serves up the generic model?

I’d love to hear thoughts on this; fire away!

Technorati Tags: