I maintain a product that has a home-grown data access layer, programmed in C++/COM for use with a Windows-based web application designer and it is at least 10 years old. This DAL is modularized with something akin to Data Providers so that it can generate basic SQL and DDL for a specific database type.
In the process of migration analysis, the biggest gap in functionality I have found is with the DDL generation. With the current product, the user creates/deletes data input forms and then data input fields and the appropriate database tables and fields are created or deleted "on the fly" (fields cannot be altered). Foreign key relationships are "soft" however, only enforced at the code level.
Through my research so far, it seems that this approach is not highly regarded for security and data integrity reasons. Which is probably the reason I can't find any tools/ORMs that have the same functionality.
I have experimented with nHibernate, which contains half of what I need with it's SchemaUpdate functionality but only does non-destructive schema updates. I have checked out other ways of dealing with a dynamic schema such as xml columns, but this greatly complicates other factors like data retrieval and reporting.
My questions ares fairly large in scope, so I am not looking for definite answers, only pointers. What are the options for continuing forward with this approach? Are there better options for allowing a dynamic schema that I am not aware of? What are the basic advantages and pitfalls of other possible approaches?