Defining a common data model for custom product types

Question

I want to create a product catalog that allows you to create complex parts for each type of product in the catalog. Product types have very different data associated with them; some of them have only common data, some with a few additional data fields, some with many fields specific to this type of product. I need to easily add new types of products to the system and respect their configuration, and I would like tips on how to create a data model for these products, as well as how to handle persistence and search.

Some products will be very versatile, and I plan to use a common interface for editing these products. Products that have an extensible configuration associated with them will receive new views (and controllers) created to edit them. I expect all custom products to have their own model, but for sharing a base class. The base class will be a generic product that does not have custom fields.

Examples of products that need to be processed:

• General product
  • Description
• Light bulb
  • Description
  • Type (listing fluorescent, incandescent, halogen, LED)
  • Wattage
  • Style (enum of flood, spot, etc.)
• Refrigerator
  • Description
  • Make sure
  • Model
  • Style (listed in the domain model)
  • Water Filter Information
    • Detail number
    • Description

I expect to use MEF to detect what types of products are available on the system. I plan to create assemblies that contain models, views, and product type controllers, drop those assemblies into the trash, and open applications for new types of products and show them in the navigation.

• Using SQL Server 2008, what would be the best way to store these various types of products, allowing you to add new types without having to create a database schema?

• When retrieving data from a database, what is the best way to translate these polymorphic objects into their correct domain models?

---

Updates and Clarifications

• To avoid the effect of the internal platform, if for each type of product there is a database table (for storing products of this type), I still need a way to get all the products that cover the types of products. How will this be achieved?

• I talked with Nikhilk in more detail about my SharePoint link. In particular, he talked about this: http://msdn.microsoft.com/en-us/library/ms998711.aspx . It actually seems pretty attractive. No need to parse XML; and there is some indexing that can be done by allowing simple and quick data queries. For example, I can say “find all 75-watt bulbs”, knowing that the first int column in a row is the power when the row is a bulb. Something (NHibernate?) At the application level would determine the mapping from the product type to the user data schema.

• Voted for a schema that has a property table, because it can lead to many rows for each product. This can lead to difficulties with indexes, plus all queries will have to essentially merge the data.

Answer 1

Use a Sharepoint-style UserData table that has a set of row columns, a set of int columns, etc. and the Type column.

Then you have a table of type types that indicates the schema for each type — its properties and the specific columns to which they map in the UserData table.

With things like Azure and other utility computing storage, you don’t even need to define a table. Each storage object is basically a dictionary.

Answer 2

I think you need to go with a data model, for example -

Product table

• ProductId (PK)
• Productname
• More details

Property table

• PropertyId (PK)
• ProductId (FK)
• ParentPropertyId (FK - Self refers to property categorization)
• Propertyname
• Propertyvalue
• PropertyValueTypeId

Property value lookup table

• PropertyValueLookupId (PK)
• PropertyId Property (FK)
• LookupValue

And then based on this dynamic view. You can use the PropertyValueTypeId coloumn to identify the type using convention, for example (0-string, 1-integer, 2-float, 3-shaped image, etc.). But in the end, you can keep everything untyped. You can also use this column to select a control template to display the corresponding property for the user.

You can use the value lookup table to save the search for a specific property (so that the user can select it from the list)

Answer 3

Summing up the results allows you to view the considered parameters for storing product information: 1) some xml format in the database

2), similar to the message above, that the number of columns with a certain number of types (shared approach)

3) through a common table with names and types stored in the lookup table and values ​​in the secondary table with column identifiers, propertyid, value (similar to # 2, however this approach would provide unlimited information about properties

4) some hybrid of the above option, in which the product table would have x common columns (for storing properties common to all products) with y user-defined columns (it could be m integer types and n varchar types). This may be the best # 2 and normalized structure, as if you knew all the properties of all products. You will get better sql performance for the properties that you use most often (perhaps for all products), while allowing you to customize columns for specific properties with each product.

Are there any other options? In my opinion, I would consider 4 above as the best hybrid of combinations.

• Dave

Answer 4

Place as much of the overall proposed structure as possible in the traditional normalized 3NF model, then add the XML columns as needed.

I do not see MEF (or any other ORM) being able to do all this transparently.

Answer 5

I think you should avoid Inner Platform Effect and actually create tables for your specialized objects. You will write specific code to manage them, so why not have proper support tables?

This will make your deployment a little more difficult - run the build and run the script - but it will probably save you a lot of pain in the long run.

Answer 6

Jeff,

We are currently using the XML field in the Products table to process all data related to specific products. Thus, our Products table contains several common fields that share all products, XML that contains everything that a particular product requires, and several computed fields that capture XML and treat some of the frequently requested fields as “virtual” fields on Product table (for example, “Style” will be set to what the current product defines, or NULL if the product does not have a Style property).

So far, we have been quite flexible with this approach - if you create some decent XSD schemes for your XML, you can even create C # proxy classes for these fields.

Works well for us - combining the best of both relational and XML worlds.

Mark