The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Design ideas for a system handling form data

Hello all, I am working on designing a system that basicly handles forms and I'm trying to come up with some new ideas.

Lets say the system has a bunch of forms to choose from. A regular user will go in, choose a form, fill it out and submit it. Administrators can go in an change the questions on the form, the options available on multiple choice questions, etc. I'm stuck on how the system should save all the answers that the user enters into the form.

We have a system in place that saves the answers in a table/column specified by the question. Therefore every time a new question gets added to a form a database administrator such as myself will have to ensure that there is a place in the database to save it. I want to get away from that.

I was just reading an article on Entity-Attribute-Value Modeling which uses one big table to store all the answers. That may be a good solution for this system. Does anyone have any suggestions or experiences to share?

Thanks for the help! I've read through them and it defnintely seems like this is the best solution for what I'm doing. I also found this on the Yale site: http://ycmi.med.yale.edu/nadkarni/In...%20systems.htm which conviced me this seemed right since what they are using it for is VERY similar to what I have to do.

I recently created a survey application similar to what you describe. I did it by having a single table that stored the question response, a reference to the survey response (stores date/time of the survey completion and provides a reference to all question responses) and a reference to the question asked.

From the reference to the question i can then determine the question type (i have a set number of question types, such as checkbox, radio, text, essay, etc) and treat the stored question response in the correct way.

All question responses are saved in plain text, with questions that have multiple choices stored on multiple lines. The only variant to this was for a special type of question type that allowed data tables to be created, for these I found the best way of storing them was to use rubys data marshalling to store a copy of the raw hash object.

Thats the basic principle anyway, it was complicated a bit by also having the possibility for questions to have an "other" response but thats not too hard to work out.