I was curious, and wanted to generate a discussion, around a standard data model used to store form data.

At a current client, we need to build 20 online forms, some with up to 100 fields (text box, text area, radio button, multi select, checkbox, single select). Instead of creating a table for each form (each field maps to a column), I would like to create a single table that is flexible enough to store values from multiple forms regardless of the variables that exist between each form (# of fields, field types, etc.). I have scene the idea of a virtual table, where you create a fixed number of columns, and then you have another table that linkes columns to fields. The application then contains the logic to ensure data is being stored and retrieved from the virtual tables. Unfortunately, I have not found any information including best practices and designs on the web that are from a good source, and was hoping someone could point me in the correct direction. I assume this is a common problem and there are design patterns or even available software to help create such a data model, and interfaces to the data. We are utilizing ASP.NET and SQL Server for the technology.

Thank you in advance for your help.

- Mike

well it sounds like a bad idea to me.... you should map your database design around the data you are storing not the forms you are using to enter it...

however here is what I would probably do if I was forced to try something like this....

1. build a form table that will store simple form details, like the name, the action and the method

2. build a formfield table that links to the form table and contains all the field information such as what form they are for what format they are and what type they are and when the data is stored

3. create a full data structure around the data being collected that maps using the information in step 2.

good luck.

You could develop a database of forms. It's not easy, and you really have to think about it, but you could dynamically duplicate and modify forms. The only thing to keep in mind is that each form would need it's own table to store the values. If you modify the form (adding, removing fields for example), you need some SQL to alter the structure of the data. In the case of removing fields, you'd be removing a column from the table, which could potentially hold data, which would be lost.

rokslide,

i did implement something similar to your solution.

i hade a form definition table with fields such as form name, create date, active, etc.

i then created a form field definition table with fields including field name, data type, maximum length, etc.

now, to store the values for each form instance, i created two tables. one was a form instance and two was a form field values table.

the issue is, the form field values table is three columns. column one links back to the form instance, column two to the field def, and column three is the field value. all values for all forms are stored in this table. therefore, the size of the table over years will grow to millions of rows (100s of forms with about 100 fields are created every month). i know this is not the most efficient way of handling this data.

i still don't want to create a table for every form, but i guess that is the only way to do it so the tables scale efficiently.

That's the normalized way to do it..