How to have a dynamic database design with dynamic values

Question

I have a set of check list which is dynamic and when the user selects it needs to saved in the database. But the attributes for the table should be dynamic. Hence we could not take the checklist values as columns for the table. As the checklist are dynamic. So help me how to design the database so that there is no performance issue. When user fill his details along with the dynamic checklist values all the details get stored to tables including dynamic. Please provide me some suggestions regarding this issue.

Answer 1

As Viruzzo was saying, all you have to do is create a relation table, like this:

**Table User**
int ID
String name

**Table CheckBoxOptions**
int ID
String description

**Table User-Checkbox-Relation**
int UserID
int CheckboxID
boolean value
int position

Then, all you have to do is add one entry to the relation table for each option a given user has in his form. When reading you read the relation table and search for all entries that have the userID that you want.

As Aaron Digulla suggested, you can save the position the the option in the User-Checkbox-Relation, using a field like the "int position" in my example. This way you can show the options in the same order every time the form is presented. :)

Answer 2

Create an XML with your attributes and store it in the database then get it from there and parse your attributes or create an array type field and store your name=value pair there.

Cheers

Answer 3

There are many ways to solve this:

1. You can create a table with USER_ID, POSITION, TEXT, STATUS if each check list is different (i.e. when there is no point to create a table for items in the list because there is no reuse)

2. If users can only select from a limited set of options for the checklists, then you should have a table for all options and create a table with USER_ID, POSITION, OPTION_ID, STATUS

3. If you always need all options and you never need to search for entries by text or status, you can use your own data structure and save it as a BLOB in the database. Advantage: You're completely free how the structure works and looks like. Disadvantage: You better get it right the first time because migrating it into a new structure is going to be hell.