I am redesigning a database schema for an application I am working on, and one of the major changes I am adding is the ability to dynamicly add new "fields" into a form based administration area.

Basically, a user can add a "large" field (varchar 4000), or a "small" field (varchar 255), and assign the field a name. The idea here is so I do not need to edit the code/database everytime we want new data to be featured

What would be the best way to incorperate this into a database schema? Doing something like adding a single varchar 4000 column would work, but that seems wasteful and I am looking for better ways.


  • Assuming SQL Server, varchar fields only take up the space used plus a 2 byte overhead storing the length. Make it as big as you need for every field. – JNK Jun 15 '12 at 21:04
  • You could store your user-defined fields in one field using xml. This has the advantage of being flexible, but also query-able (several dbs have good xml querying ability) – Neil McGuigan Jun 16 '12 at 19:51
  • which database? – Asya Kamsky Jun 17 '12 at 7:56
  • this application is powered by MySQL – Señor Reginold Francis Jun 18 '12 at 13:46

ability to dynamicly add new "fields" i guess you are talking about UDF (user defined fields) to achieve this you have few methods but most well known is to have a table with 3 fields


or Just


Where basically you store column name and value as rows, because you can easily add and delete rows, this method has allot of disadvantages and its ugly design, but it gets your job done.

you also have the method of adding few extra fields at the end of you table, and keeping them for incase the user decided to use them


Customers Table

in this method the user is only limited to a max of 5 user defined fields, but atleast you can make use of the RDBMS structure and table relations etc. it has its pros and cons as well.

This question is asked on SO and lots of good answers were posted you can check it click here

i also suggest looking at NoSQL , because when it comes to requiring a capability to freely add columns it means you lose the benefit of an RDBMs of SQL and relation ships of tables, so its a good idea to look at the capabilities of NoSQL and its flexibility.

check out

  1. Neo4J
  2. Mongo DB
  3. Couch DB

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.