Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thanks

share|improve this question
    
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

1 Answer 1

up vote 1 down vote accepted

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

Table
-AppName
-ColumnName
-Value

or Just

Table
-ColumnName
-Value

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

e.g

Customers Table
-Name
-Tel
-Country
-....
-UDF1
-UDF2
-UDF3
-UDF4
-UDF5

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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