Hi All,

I'm working on a legacy system that allows certain tables to be dynamically extended. Each customer has their own database, most of which is a static schema that is common to all databases. However, there is the ability to add custom columns to certain tables. For example, on a table called "Contacts", a user can say that they want a field called "Age" and the system will add that column to the Contacts table using DDL. The Contacts table has static columns like Name and Address, but the user can extend it with whatever they want. I am stuck with this schema and cannot change how the database works.

I have been looking all over for someone who has had the same problem, but haven't been able to find a solution. Has anyone had the same problem and found a solution?

I was thinking that my Contact objects could have a dictionary property that used the column name as the keys for these dynamic columns. Is there anyway to map such a schema using Hibernate?

Thanks in advance,
Matt

What do you mean by dynamically add columns?
Do you mean that at setup time you do this? Or do you mean that the customer get up in the morning, adds a column to the table and starts using that?

If the first, you may want to try a derived class approach, where you have a common Contact class and then you have a XyzContact, AbcContact, etc.

If the second, this is going to be harder, but maybe you can try do that using dynamic components and modifying the mapping configuration on the fly.
Not fun.

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".


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.


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:


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

That being the case, you need to do the following:

* When the user adds a new column, add it to the table, add it to the fields table.
* Rebuild the session factory.
* When building the session factory, you have the Configuration object, through this object, you can access the class configuration, and from there, dymaically (using the Fields table) define the dynamic component fields.
[/list][/list]

But if you say that you can change the domain objects and maybe you can add them, then you can introduce many-to-one relationship. This will require one extra table in a database (the "Custom Fields" one).

Then you also need to introduce a class for custom field.

Ayende, I understand what you are suggesting, but I don't think it's feasible for my application. I would rather write my own custom code to map the custom fields for the few objects that require it. As you say, it would not be fun trying to dynamically manage the SessionFactory. Thank you for your suggestion though.

Roland, I'm not sure I understand what you are suggesting. What is the many-to-one relationship that you are suggesting? There is a logical many-to-many relationship between the Contact object and the CustomField object, but since all the values are stored in a single row of a single table I haven't been able to figure out how to map it. That is where I get stuck. I do have full control over the domain objects, so how do I model them to fit the database schema and what is the mapping strategy used?


Thanks again in advance,
Matt