What is the best way to allow a user to add columns to their database on-the-fly? [closed]

Question

We're looking at designing an application for managing contact information.

One of the customer's requests is to allow their powerusers or admin-type staff to add columns to the database through the UI. Customers do not have direct access to the database (i.e., they can't open it up with SSMS and make changes).

I see controls for 1)entering the name of field, and 2) entering the data type, and 3) selecting the table to which the field should be added. Once the user clicks a button, the database is updated with the new field. I'll need to check for proper permissions, does the field already exist, etc, etc. We'll also need to wire up the form fields to this new field, like a report designer interface.

1. Where can I find a sample that shows what I want to do?
2. What are the drawbacks to doing this? Aside from what appears to be a decent amount of work for the developers doing this.
3. Would you recommend doing this? If not, what is a better solution?

Edit - We are required to use SQL Server. Not negotiable.

Answer 1

Is it an absolute requirement that they add columns to the table, or just that they be able to specify additional fields to store? I would strongly suggest you consider something like Entity-Attribute-Value rather than allowing the end-user (admins count as end-users) to make schema changes.

For something like this, you'd have a table to define your custom fields, then a many-to-many association table to allow the user to specify a value for a custom field for the contact. For instance:

    Contact
    ---------
 -> ContactId
|   FirstName
|   LastName
|   etc.
|
|                        ContactField
|                        --------------
|                        ContactFieldId <---
|                        FieldName          |
|                                           |
|   ContactFieldValue                       |
|   -------------------                     |
 -- ContactId                               |
    ContactFieldId -------------------------
    Value

The specifics of implementation are obviously up to you (for instance, whether to use ContactId + ContactFieldId as a compound primary key in ContactFieldValue), but this should get the general idea across.

Answer 2

I would strongly push back the requirement and let them know what an incredibly bad idea it is. Yes you could add an EAV table for these, but then you have the problem of querying it which is neither simple nor performant. Or you could use the table with half a dozen spare columns as suggested by @Jerry Coffin but his leads to difficulties in reporting and what happens when they use them all. All lead to lots of extra work by the devs to support a requirement that will probably not result in 3 database changes a year. You are risking your data integrity, your database performance and your accuracy in reporting (I bet these new fields won't have PK/FK relationships or default value or even be the correct datatype so that dates are varchars and thus data will be added to the database that the dues date is ASAP which won't work for reporting) and and all so some people who don't know what they are doing can pretend to make database changes that are ineffective and poorly thought out.

I've worked with many COTS products that allow this and it never turns out well when people actually add columns (and very few people actually are brave enough to do so fortunately). They don't go on the forms where they need them, they aren't automatically added to reports, they often aren't searchable, and in each case they ended up spending more money to get someone to fix the data mess they made than it would have cost to get a professional to make a design change.

And most of the time when I have seen a requirement like this is because the senior managers think flexibility is cool, not that any of the power users actually need or even want to do this. They need to be aware of the cost of what they are asking for.

It is far better to spend and extra few days talking to the power users about what they need and getting the design right to start with than to go down this route.

This is one of those requirements that really needs a long serious discussion as to how much it will cost not just in dev hours but data integrity and performance and maintainibility for almost no gain. I would do a formal cost benefit analysis to show them exactly how bad the idea is. Then once they are fully infomed as to the utter stupidity of what they are asking for, if they still want it, go ahead and build it and start looking for a new job because you don't want to be the one who has to maintain this.