Allowing end users to dynamically add columns to a table

Question

We are developing a .NET application with a SQL Server back-end. The client requests the ability to dynamically add custom attributes to entities after the application has been deployed.

As suggested in a similar question we could create a table which would then contain a row for each custom attribute value (Entity-attribute-value model). However, we are considering allowing end users to actually modify the table (also suggested in the same question) i.e. adding and removing columns.

(Edit: as noted in the comments, DDL wouldn't be executed directly by users or the application, but through stored procedures ensuring that everything runs smoothly)

Main reasons are:

• Improved performance/searchable attributes
• The attributes are almost always required to appear as columns e.g. in a data grid in the user interface or when extracting data for further processing in Excel/PowerPivot.
• Data is strongly typed (as opposed to storing all attribute values as varchar)
• A simplified data model

Are there any caveats that we should be aware of?

Things that come to mind are:

• Backup/restore operations that might be unable to handle the changing data structure
• Dependent objects (such as views) that aren't properly updated to reflect these changes (a dependent view would have to perform a select * from table in order to include any added columns).
• ...

Any input regarding this approach is greatly appreciated.

Answer 1

I work with a third party application that handles this in a variety of ways:

1. Most tables have a 'custom' version of the table with various fields to hold generically named data types: Number1, Date26, Text3, etc.). So there is Company and CompanyCustom that have a 1-1 relationship.

2. Lists are created on a table that has a ListID (and a corresponding way for users to setup the schema) and foreign key to link to a main table. This table has several generic columns like #1.

3. create your own tables

4. create your own views and stored procedures and register them in the application. These datasets can them be attached to data grids and/or used in custom reports.

There is an interface to the user can label their columns as they see fit( i.e. Text1 = "Blah Blah Blah"). There are plenty of wasted fields in this situation (although my company has managed to use most of the fields including Money47) and it is not ideal for performance, you can't beat the near limitless flexibility we have.

The key here is how much is this client willing to pay for this capability along with the on-going support? If you let them create custom fields on an existing table and they decide they want to change the data type that won't convert smoothly, are they going to expect you to shuffle and convert it?

We could hire a full-time programmer for what we pay for this system. SalesForce.com and similar sites have this capability. I don't think you want to get into this for a one-off client app. They may as well pay you to keep updating the app in the long-run.