I'm involved on a project to make a survey system. We've been hammering out the logic for a few question types, and I could use a second opinion on what is the best way to proceed. We work on a ASP.NET 2.0 website using VB(VS2005) with an Oracle database.
In our oracle server, we plan for some tables to organize our data. There's a table for our surveys, one for questions (keys determine which survey it goes on), one for answers (again, keys determine what question it belongs to), and one for answer collection. Most questions only return one response, and that's pretty easy to figure out. However, when we start thinking about items that return multiple answers, it starts to get tricky.
For example, if we have a simple matrix of 3x3 filled with check boxes. The rows are days 'Monday', 'Wednesday', 'Friday'. The columns are activities like 'Biking', 'Running', 'Driving'. The user checks each one they did for a given day, thus each row can have more than one response. Another one we want to think about is what if instead of checkboxes, we have textboxes where users write in a value of how many minutes they spent on an activity.
So far, for collecting responses, I like the idea of traversing a list of controls in the form and keeping tabs on the kinds of controls that collect data. Since the controls are created in code, usually they're given an ID of a string with a number affixed to the end to keep track of what question type it is, and what number it is.
Question #1: Should the data returned from the user be in a single database entry with delimiters to separate each answer, or should each answer get it's own entry?
Question #2: What's the best way to identify what response goes with what answer (on the survey)?