I am trying to find a tutorial that will guide me on how user based custom fields works. Like in surveys sites, where they let users create custom fields and store them and more importantly store the data entered through those fields.
I am looking for something that describes how this is done on database. I am having a little trouble finding a way that would not take forever retrieving this data once the users whats to extract it to excel/cvs files.
The Entity-Attribute-Value model is normally used to handle this scenario in a relational database. A quick search for "EAV model" will yield more info than you'll know what to do with.
Sixth Normal Form is the formal way to implement this. Go with 3NF for all tables, and 6NF for the one or two tables that you need to add columns without DDL changes. Use sparingly.
EAV is the bastard son of 6NF. What that means is, people who do it, and write about it, do not have a formal understanding of 6NF, so often the create monstrosities.
Of course, you must retain good standards: use datatypes; Declarative Referential Integrity (Foreign Keys); etc. Do not give those up obtain anything. Run like hell away from anyone telling you you have to give them up.
6NF/EAV is very fast, there is no hindrance to using the set-processing capability of the server. Again, run like hell away from anyone who tells you that you have to use row-by-row processing or cursors or that you cannot build the columns from the rows easily. Post again if you have specific problems.
This requires going beyond the current capability (controls, DDL) of SQL; in order to do that in a controlled fashion and avoid creating unmaintainable monsters, you need a small catalogue, to contain the meta-data. If you are clever, you can use it generate the SQL reruired for querying, and thus eliminate a lot of otherwise manual labour.
There is a lot of misinformation around, and some people with "rep" are clueless. In order to succeed technically, we need accurate info, not myths and fear mongering. You may be interested in a recent post in which I tried to set the recond straight.
We use 3 tables for this per table where we need to support user defined fields. So for instance, if you want to apply this to your SURVEY table, you could create:
SURVEY_ATTRIBUTE
- SurveyAttributeId
- SurveyAttributeName
- SurveyAttributeType
SURVEY_ATTRIBUTE_CHOICE
- SurveyAttributeChoiceId
- SurveyAttributeChoice
- SurveyAttributeId
SURVEY_ATTRIBUTE_VALUE
- SurveyAttributeValueId
- SurveyId
- SurveyAttributeValue
The SURVEY_ATTRIBUTE table stores one record per custom attribute. The SURVEY_ATTRIBUTE_VALUE table stores the attributes that are actually assigned to surveys. So if an attribute does not apply to a servey, nothing is stored. The SURVEY_ATTRIBUTE_CHOICE table stores all allowable choices for attributes of type 'LIST'.
The SurveyAttributeType field in the SURVEY_ATTRIBUTE table is used to describe the type of the attribute. We only use a small number of allowable types like CHAR, DATE, NUMBER, LIST. Depending on that value, our application knows what to do with the value stored in the SurveyAttributeValue field. You could of course formalize this further to allow a broader range, specify maximum fieldlengths etc..., it all depends on the level of freedom you wish to give your end-user. We try to keep it as simple as possible for our target audience is not database administrators but end-users, they usually don't care about fieldlengths and such.
You could also choose to skip the SURVEY_ATTRIBUTE_CHOICE table and store your allowable values in an XML string in the SURVEY_ATTRIBUTE field. That will depend on the way you are going to implement in your application.
