Creating a schema for various sizes of varchar

Question

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

Answer 1

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