A common theme in many application is the need to support custom / dynamic fields. In other words, the system admin may decide that the Customer needs to have a few additional fields that aren’t part of the mainline development.

Entity Attribute Value – store everything in a n EAV model, which basically means that you are going to have tables named: Tables, Rows, Attributes and Values.

Dynamically updating the schema.

In general, I would recommend anyone that needs dynamic fields to work with a data storage solution that supports it (like RavenDB, for example). But sometimes you have to use a relational database, and NHibernate has some really sweet solution.

First, let us consider versioning. We are going to move all of the user’s custom fields to its own table. So we will have the Customers table and Customers_Extensions table. That way we are free to modify our own entity however we like it. Next, we want to allow nice syntax both for querying and for using it, even if there is custom code written against our code.

This is fairly basic so far, and not really interesting. We expose a hashtable as the entry point for a dynamic object that exposes all the dynamic fields. The really interesting part happens in the NHibernate mapping:

As you can see, we used both a <join/> and a <dynamic-component/> to do the work. We used the <join/> to move the fields to a separate table, and then mapped those fields via a <dynamic-component/>, which is exposed an IDictionary.

Since we want to allow nicer API usage, we don’t expose the IDictionary directly, but rather expose a dynamic object that provides us with a nicer syntax.

I think the main advantage of this approach is the huge performance benefit over an EAV model. Unfortunately I don't see a way to get this working in a multi-tenant architecture, where different tenants need to have individual fields, while still using on the same DB.

Whether different databases and schemas for different tenants are a good choice depends on the kind of software. Let's say you have a SaaS - based CRM-application and you want your customers to choose which fields make up a single business-contact. You need to find a way, to dynamically allow to customize the contact-entity.

In such a case it would not only be a waste of memory (each db-instance can take up to hundreds of mb) but also a unnecessary overhead while managing updates, backups, etc.

@Oren:

Would you say that RavenDB would be a good choice in such a situation?