How do you build extensible data model

Question

Hi,

I'm thinking of building a ecommerce application with an extensible data model using NHibernate and Fluent NHibernate. By having an extensible data model, I have the ability to define a Product entity, and allow a user in the application to extend it with new fields/properties with different data types including custom data types.

Example: Product can have an addition fields like: Size - int Color - string Price - decimal Collection of ColoredImage - name, image (e.g. "Red", red.jpg (binary file))

An additional requirement is to be able to filter the products by these additional/extended fields. How should I implement this?

Thanks in advance.

Answer 1

Possible option would be to store all extra fields in an XML structure and use XPath/XQuery to retrieve them from the database. Each extensible entity in your application will have an XML field, like ExtendedData, which will contain all extra properties.

Answer 2

I think this link describes kind of what you want...

http://ayende.com/Blog/archive/2009/04/11/nhibernate-mapping-ltdynamic-componentgt.aspx

More info on dynamic-component:

http://www.mattfreeman.co.uk/2009/01/nhibernate-mapping-with-dynamic-component/ http://bartreyserhove.blogspot.com/2008/02/dynamic-domain-mode-using-nhibernate.html

The idea behind dynamic-component is that you can build your data model by not having a one to one mapping of databse columns with properties. Instead you have only a dictionary property that can contain data from as many properties as you like. This way when you fetch the entity, the dictionary gets the data of all columns configured to belong in there. You can extend the database table's schema to include more columns and that will be reflected to the databse model if you update the mapping file accordingly (manually or though code at application start).

To be honest I do not know you can query such entity using the "attributes" property but if I had to guess I would do an IN statement to it.

Answer 3

One of the options is EAV model (Entity-Attribute-Value).

This model is good to apply if you have a single class in your domain, which table representation would result in a wide table (large number of columns, many null values)

It's originally designed for medical domain, where objects may have thousands of columns (sympthoms).

Basically you have

Entity (Id) (for example your Product table) Attribute(Id, ColumnName) Value(EntityId, AttributeId, value)

You can have some additional metadata tables.

Value should better be multiple tables, one for a type. For example: ShortStringValue(EntityId, AttributeId, Value nvarchar(50)); LongStringValue(EntityId, AttributeId, Value nvarchar(2048)); MemoValue(EntityId, AttributeId, Value nvarchar(max)); IntValue(EntityId, AttributeId, Value int);

or even a comple type: ColorComponentsValue(EntityId, AttributeId, R int, G int, B int );

One of the things from my experience is that you should not have EAV for everything. Just have EAV for a single class, Product for example. If you have to use extensibility for different base classes, let it be a separate set of EAV tables.

Onother thing is that you have to invent a smart materialization strategy for your objects. Do not pivot these values to a wide row set, pivot just a small number of collumns for your query criteria needs, then return a narrow collection of Value rows for each of the selected objects. Otherwise pivoting would involve massive join.

There are some points to consider: . Each value takes storage space for foreign keys . For example row-level locking will behave different for such queries, which may result in performance degradation. . May result in larger index sizes.

Actually in a shallow hellow world test my EAV solution outperformed it's static counterpart on a 20 column table in a query with 4 columns involved in criteria.

Answer 4

Another option is to use Non-relationnal Databases which are typically suited for this kind of things.

NOSQL databases(couchDB, mongoDB, cassandre...) let you define dynamically your propretyfields, you could add fields to your product class whenever you want.

Answer 5

I'm searching for similar thing and just found N2 CMS (http://n2cms.com) which implements domain extensibility in quite usable way. It also supports querying over extension fields which is important. The only downside I find out is that it's implemented using HQL so it would take some time to reimplement it to be able to query using QueryOver/Linq, but the main idea and mappings are there. Take a look on ContentItem, DetailCollection, ContentDetail classes, their mappings and QueryBuilder/DetailCriteria.