Hi,

Let me start with a scenario. Let's say I have a contact management system. The users will be able to add their customers information into the system. If they need more fields, they will be able to add it via the application (for example; let's say second business phone or fax number). Technically I'm looking for user defined fields. What is the best way to handle this in database design? The database should be flexible and scalable.

I've seen this kind of functionality in CRM type software. Any idea?

Thanks,
semaj

This is a horrible way to design a database, and is indicative of poor requirements gathering.

Why "poor requirements gathering"? Could you please tell me what you meant?

Thanks,
semaj

I mean that rather than taking the time to figure out what your clients need, you are trying to throw in functionality to let them define it themselves on the fly. That is a recipe for a disorganized, buggy, and unreliable database design.
Software companies like to advertise this sort of customizability as a feature, but in reality it is a license to let the client screw things up as many ways as they desire. I have worked with so many hosed up databases like this that it is ceased being funny.
Why not take it to its logical conclustion? Give the client a completely customizable application that they can configure any way they want? In other words, nothing but a CREATE DATABASE statement. That should make them all happy.

That is the customer need rather than my functionality. I just want to get some idea how an application can have such a user defined fields (not all, but some udf).

And again, if the customer NEEDS (not requirements) are well defined, there is no requirement for unneccessary "spare" fields.

anything that you put in place is going to be a kludge. If you try spare text fields, you will also HAVE to add a "shadow" table to hold the table name, field name, field "type", field input mask, limits, etc. Then, you'll have to enforce data conversion, validation, etc, either in convoluted triggers, or in self-modifying constraints, which would be a disaster waiting to happen.

I agre with blindman here - research the pros (none in a well defined system) and cons (way more expensive, both up front, and in maintenence, way more fragile, way more complex,) and show the customer why this approach is a bad idea, and one that will cost them MUCH more than they would ever want to pay, in dollars and downtime.

There simply is no good way to handle this. Having application code make modifications to table schemas at runtime is undoubtably the WORST method. If you absolutely have to do this then throw a couple of spare undefined columns onto your table to start with. And make sure you write a support contract that favors you, because you will be getting lots of calls.

I'm not in support of the approach either but.... if you do go down this like then I would throw in a a few spare columns of each data type group (say varchar, int, datetime) to get at least a modicum of data constraint.

pootle flump ur codings are working excelent.

I too would also like users on the web to be able to add their own fields.

I believe that this is a real requirement. Simply dismissing this as poor requirements gathering is silly when you have no idea of what the requirements are.

From what I have understood so far, it seems a challenging problem with no decent technical solution. The standard solution seems to be creating multiple columns for different types. Obviously this sucks since its not a normalized DB structure.

Any more thoughts on this? I know MS Sharepoint supports dynamic fields. I wonder how they do it?

Is this a constraint of DBMS in general?

I was interested to find out Microsoft's solution to store custom user fields, since you would expect them to use the best possible solution.

After a few google queries on MS's sharepoint database schema, I found this page:
http://msdn2.microsoft.com/en-us/library/ms998711.aspx

Basically a UserData table with 201 columns!!!! hehehe... how is that for database design!?

I would like an airplane where passengers could add additional seats, wings, flaps, and cargo space during the flight!
Please don't dismiss this idea as silly, because it would be really really cool.
From what I understand, it seems a challenging problem with no decent technical solution.

Ah, to be young and naive once more....

Since this thread couldn't provide any intelligent discussion on this topic, I went and did a little more research on Google.

Some of you may find this useful.

Blogs discussing different techniques.
How do you extend and customize a database?
Database Schema to Support Customizable/Extensible Application

I found this to be the best article that provides pros and cons for extensibility patterns.
Multi-Tenant Data Architecture

I've still not decided on a database design yet, but I want to take my time to find a good balance between scalability and flexibility for users.


In the case of a contact management system, I too would agree that an extensible design is a bad idea. It is clear what sort of data users will want to store, so its just a case of identifying those requirements. My system is a little different, since it is not possible to guess at what sort of data might be stored.

Thanks for checking back and posting the articles.

I'm with you Blindman!

Dear andymagic1:

thanks for researching the topic and providing the links, this is indeed very useful.

I am currently facing a requirement that is forcing me to go down this path of a flexible schema, although it is certainly not my preference.

It has been a couple of years since your last post, and I would like to know what kind of experiences you have had with this approach. Did you ever go into production with such a flexible schema? What would you do differently now? Any particular pain points that you can highlight? Recommendations in general?

Thank you in advance.