design choice for a data structure customizable by the user?

Question

I'm planning to build an application that allows the user to set up its data model (i.e. create fields, data structure, etc.) dynamically.

I'm facing several technical possibilities, all having drawbacks. :

1. In admin screens, update the SQL schema of the DB to reflect the changes.

• I fear this is a terrible idea because of the permissions the application must have on the DB. Moreover, if a new SQL schema must be applied on every click, I imagine I will run directly on a hole. This is the approach I've seen used by most applications customizable by the user.

2. Create a set of generic extra columns in the DB schema and hope there are enough columns for the complex data models.

• This will quickly become a functional limitation if I can't allow more than X columns in my app.

3. In a single table, store all items with an ID column and an XML column to store the user-defined columns.

• This approach may remove the previously mentioned drawbacks because the SQL schema will remain static, but as EF (which I was hoping to be able to use) does not know how to manage XML data type, I will have to end up with either manual SqlCommands with an XML function or writing a custom EF provider, which I imagine will be quite a lot of work.
• This is the approach chosen by Microsoft for SharePoint... this makes me think it's the best one (or at least the least bad).

4. Create a "properties" table with basically just an itemId column, a property name column, and a property value column.

• This approach implies a very very large table (X items * Y properties per item)
• I will have to store my values in plain text, even if it's numerical.

My requirements are :

• Keep the code maintainable, unit-testable, and all-fashioned techniques
• Have a responsive application with a large amount of data
• Have an as secured as possible application
• Allow the users to fully customize their application (create a custom view with filter/sort on user properties).

I feel the choice of the correct design must be the best one now because it would be tough to change this later.

Any feedback would be appreciated.

Answer 1

One option would be to use a NoSQL database like MongoDB which is schema-less. New fields don't need to be defined up front (no schema modification headaches) and different records can have different fields. This is one of the benefits of a NoSQL store like this.

e.g. in mongo, your "table" could have these 2 records within in legimitely:

{
    "ID" : 1,
    "FirstName" : "Joe",
    "LastName" : "Bloggs",
    "FavouriteColour" : "Blue"
}

{
    "ID" : 2,
    "FirstName" : "John",
    "LastName" : "Smith",
    "DOB" : "2000-01-01"
}

Adding in a new field is as simple as just start including it in records.

In my experience, having a fully flexible/dynamic schema in a RDBMS like SQL Server can be a bit of a pain and be challenging to achieve high performance. I've had experience with options 1) and 3) that you listed. When data was stored as XML, I ended up usually needing to shred the data out into relational form anyway for certain purposes.