How to design a SQL database for User Defined Fields?

Question

1.00/5 (1 vote)

See more:

I build Project that give the client the power to add new section,new field in form,
Need to be able to dynamically add User-Defined fields of any data type Like "date", "boolean" or "text"

There are many Options :

1) Create a big table with StringValue1, StringValue2... IntValue1, IntValue2,... etc. I hate this idea, but will consider it if someone can tell me it is better than other ideas and why.

2) Create a dynamic table which adds a new column on demand as needed. I also don't like this idea since I feel performance would be slow unless you indexed every column.

3) Something else?

Any Help

Posted 5-Oct-13 11:53am

Ahmad Al Halabi

Updated 7-Oct-13 0:12am

v5

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2013-10-05T14:33:00

not sure exactely what you mean ...

On the rare occasion I have had to design a table, that may require extra columns later on, I usually do something like add :-

user-field-1-text-value text
..
user-field-5-text-value text
user-field-1-int-value int
..
user-field-5-int-value int

for the values (and allow null values), then have a corresponding text field for each user field that holds 'what the value represents', so ..

user-field-1-text-description text
..
user-field-1-int-description text
..

for say SQLite - if you think you might need user date fields, then add a number of those

I have seen commercial products that do the same sort of thing, ultimately I think 'typing' the fields is better that forcing converts, but Im not sure its a great way of doing it ...

[Edit : it really, really, depends on your circumstances/requirements, and how much flexibility you need - but it occurs to be you could define a 'blob' (or clob) type field, and do the same sort of thing with/by storing JSON in that field - means a lot more work though /Edit]