Flexible Persistence Layer

Question

I am designing an ASP.NET MVC 2 application. Currently I am leveraging Entity Framework 4 with switchable SQLServer and MySQL datastores.

A requirement recently surfaced for the application to allow user-defined models/entities to be manipulated. Now I'm unsure if a SQL/relational database is appropriate at all; instead of adding/removing 'Employee' objects, for example, the user should be able to define an 'Employee' and what properties it has - effectively adding/removing tables and columns on the fly, at runtime.

1. Is SQL unsuitable for this? Are there options which allow me to stay within a relational database structure and still satisfy this requirement? Within the Entity Framework, can I regenerate .edmx files 'on the fly' or are there alternatives which achieve similar goals?

2. I've looked briefly at other options like 'document-based' dbs and 'schema-free/no-sql' dbs, such as MongoDb. I've also looked at some serialization formats such as Google's Protocol Buffers, JSON, and XML. From your experience, are any of these particularly suitable for this purpose? Serialization performance is not a big concern.

The application is in its infancy and I have no time constraints. Essentially I am free to rewrite it as I please, so if scrapping and starting over is a better alternative, I am very open to this. What are your suggestions? Thanks in advance!

Answer 1

Before looking at options I'd suggest (if you have not already done it :-) that you need to get a clear definition of exactly what users will be able to define. Once you have that you can then deduce an idea of the level of flexibility needed and therefore the type of data store needed to do the job.

One other word of advice would be that if they clients demand to be able to create anything any way they want - walk away. I've dealt with clients and users at all levels and one thing that is guaranteed is is that users have no interest if the effective and efficient design of data and therefore will always reduce the data to a pile of poo through shear neglect.

You need to set some boundaries so that the data store behind the system maintains some integrity.