Thanks for your reply. It is, of course, the more complicated option. A user can log in at any time and add, remove or modify their custom fields. In the current system when this happens, DDL statements are issued to the tables that modify them accordingly. I will try to give an example to be more clear.
The following table called "Contacts" has a ContactID column and one static column called "Name". This customer has also added two custom fields to their contacts, one for "Age" and one for "Favorite Color".
Code:
|------------------------------------------------|
| ContactID | Name | CustomField1 | CustomField2 |
|------------------------------------------------|
|------------------------------------------------|
| 1 | Bill | 38 | Blue |
|------------------------------------------------|
| 2 | John | 42 | Green |
|------------------------------------------------|
There is another table that gives meaning to the custom fields. It is called "Fields" and points to where the data for each field is stored.
Code:
|------------------------------------------------------|
| FieldID | Name | TableName | ColumnName |
|------------------------------------------------------|
|------------------------------------------------------|
| 1 | Age | Contacts | CustomField1 |
|------------------------------------------------------|
| 2 | Favorite Color | Contacts | CustomField2 |
|------------------------------------------------------|
Remember that each customer has their own database. If this customer wakes up tomorrow and decides that they also want to track the favorite song of all their contacts, another row would be added to the "Fields" table and another column would be added to the "Contacts" table.
I cannot change how the database works. What I do have control over, and can change to suite my needs, are the domain objects. It's doesn't seem feasible to me to have different definitions for my domain objects, nor do I want that. What I imagine is something like this:
Code:
class Contact
{
public int ContactID;
public string Name;
public IDictionary CustomFields;
}
All of the custom field values would be stored in the CustomFields dictionary. Now all I need to do if find a way that NHibernate can map this :-) I've looked at dynamic components but it doesn't seem to be what I'm looking for. I've also looked at mapping a custom type but I can't figure out if it will suite my needs. Is it anyway possible to map a collection (such as a dictionary or a list) to a flat table structure dynamically? That's basically what it comes down to.
Thanks again in advance,
Matt