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.

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:

Simplicity (It’s flat and easy to understand)

Indexing (I have created indexes for the first 2 columns of each data type)

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:publicclass Product

2: {

3:publicstring ProductName { get; set; }

4:

5:protectedinternalbool? bit0 { get; set; }

6:protectedinternalbool? bit1 { get; set; }

7:protectedinternalbool? bit2 { get; set; }

8:protectedinternalbool? bit3 { get; set; }

9:protectedinternalbool? bit4 { get; set; }

10:

11:protectedinternalbyte? tinyint0 { get; set; }

12:protectedinternalbyte? tinyint1 { get; set; }

13:protectedinternalbyte? tinyint2 { get; set; }

14:protectedinternalbyte? tinyint3 { get; set; }

15:protectedinternalbyte? tinyint4 { get; set; }

16:

17:protectedinternalint? int0 { get; set; }

18:protectedinternalint? int1 { get; set; }

19:protectedinternalint? int2 { get; set; }

20:protectedinternalint? int3 { get; set; }

21:protectedinternalint? int4 { get; set; }

22:

23:protectedinternaldouble? float0 { get; set; }

24:protectedinternaldouble? float1 { get; set; }

25:protectedinternaldouble? float2 { get; set; }

26:protectedinternaldouble? float3 { get; set; }

27:protectedinternaldouble? float4 { get; set; }

28:

29:protectedinternal DateTime? datetime0 { get; set; }

30:protectedinternal DateTime? datetime1 { get; set; }

31:protectedinternal DateTime? datetime2 { get; set; }

32:protectedinternal DateTime? datetime3 { get; set; }

33:protectedinternal DateTime? datetime4 { get; set; }

34:

35:protectedinternalstring string0 { get; set; }

36:protectedinternalstring string1 { get; set; }

37:protectedinternalstring string2 { get; set; }

38:protectedinternalstring string3 { get; set; }

39:protectedinternalstring string4 { get; set; }

40:

41:protectedinternal Guid? guid0 { get; set; }

42:protectedinternal Guid? guid1 { get; set; }

43:protectedinternal Guid? guid2 { get; set; }

44:protectedinternal Guid? guid3 { get; set; }

45:protectedinternal Guid? guid4 { get; set; }

46:

47:protectedinternaldecimal? money0 { get; set; }

48:protectedinternaldecimal? money1 { get; set; }

49:protectedinternaldecimal? money2 { get; set; }

50:protectedinternaldecimal? money3 { get; set; }

51:protectedinternaldecimal? money4 { get; set; }

52:

53:protectedinternal 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 inthis._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.

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.

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:

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?

Your Comments.

Product is a DAL type, and FurnaceFilterProduct is a presentation-tier type. So its a projection scenario and some app logic maps one to the other and vice versa, if not for anything other than simplicity of the code.

You might do the projection on the server, or all the way on the client... depending on the app, who chooses the set of specific types etc.

I chatted with Glenn Block (from the MEF team) last night, and he also suggested that MEF might not be the best solution here. He said that I'm basically recreating what an ORM can do. I hadn't have thought of the problem in that way, but it makes total sense.

In order to allow new model projections to be added easily, I am going to look into using NHibernate. Perhaps I can create a separate config file for each projection, and package that with each new product type that I roll.

This could very well be a case of tunnel vision, where I had my mind set on using MEF to solve this problem, where it might not be the right tool for the job.

For the View extensibility, who knows, perhaps MEF will play a role there.

I'll post an update after I dig into NHibernate -- I've never used it before, but this is a great reason to tinker with it.

Sorry if this sounds ignorant, but wouldn't this be a good situation to use the Entity Framework? A base class or interface to define the basic capabilities of all products, and then for specific products, store a XML metadata record (or heck, even an external xml file) that would be dynamically loaded and applied against an entity, giving it specific properties for that product and telling the Entity Framework how to map the implementation to the db. This is similar to nHibernate and NetTiers, but possibly less overhead.

I'm just diving in the Entity framework as well after using LINQ to SQL for a bit. The possibilities with it are pretty amazing. If you wanted to stick with somewhat familiar territory, I do know that with LINQ to SQL you can abstract out the mapping metadata to an XML file that you can load at runtime. It allows for some great decoupling from your data schema (not entirely of course), though I'm not sure what advantages it might have over the Entity Framework's features.

In an enterprise, you could store the rulesets on a central server which exposes a business rule service for a published list of object types. You query the server for the desired rulesets, grab them, then execute them against the objects (in only a couple lines of code, no less!) and voila! As relates to your project, product entity validation could be stored in a similar fashion, thus abstracting your business rules very cleanly from the schema and implementation.

Hi, I looked at your problem and saw that you posted this at multiple sites. I hope you found an answer since I am trying to solve a similar problem. The link below is an article that I think might help. I haven't tried it yet since I am just trying to find options.