Database design for online forms system (paper forms to electronic)

[sbrett]

Hey everybody, I have a bit of a complicated project, so here's the details and what I've done so far (sorry for the novel!):

I've been asked to convert paper forms to electronic only for a school. My guess is there will be anywhere from 30-50 forms online when it's all said and done. Example of types of forms:

• Add/Drop - students can enter a class and section number and choose to add or drop a class. They can add or drop as many classes as they want.
• Change of Name - students can enter their 'old' name and their 'new' name, as well as uploaded supporting documents (marriage license, driver's license, etc.)
• Update form - students who are coming back to school can enter a list of all the schools that they previously attended, and can add as many as they need.
• Change of Major - students can select a new major based on dynamic dropdown lists.

Those are just a few examples. As you can see there are a few that just have a bunch of standard textboxes, and there are a few that have a more dynamic interface.

Current database design:
There's actually already an online forms system in place. It basically consists of these tables:

• Student - to hold name, address, etc.
• Form - contains a field for every possible field on every possible form
• FormSubmission - contains details about a particular submission (date submitted, last person to review it, etc.)
• Other tables to store the 'dynamic' data that gets entered. IE: 'courses' table to store the courses entered from the add/drop form, 'attachments' table to store paths to attachments for any forms that have attachments, 'school' table to store a list of schools for any forms that let students enter schools, etc.

This all works, but it is a nightmare to maintain. There is one .aspx page for each form, and each form has its own processing logic. The 'form' table has hundreds of columns (one for every possible field in every form)--this seems very inefficient.

I thought about having one table per form (Table_AddDrop, Table_ChangeOfName, etc.), but I was wondering if anyone has other suggestions. I was trying to go for a more 'generic' approach and have an 'responses' table that stores the responses (this way I don't have to specify every single form field in the database). Maybe with columns such as:

FormSubmissionID (to tie to the submission table), and then a column for each possible datatype: StringAnswer, IntAnswer, DoubleAnswer, DateAnswer, BooleanAnswer, BinaryAnswer

This way I can store the responses of a form no matter what kind of form it is. Does anyone have any other suggestions? How is an online forms system data model typically set up? I've looked everywhere for good examples, coming closest with the various survey systems, but none really meet the requirements. It seems like this would be a common project.

Attached to this post are database diagrams of the old and new (in-progress) data models

[BitBlt]

I like the way you're going with this, but I do have one suggestion.

On the "Answers" table, you might consider having a single variable length string to hold any kind of answer, then a separate type column to say how to decode the answer. So, if you got a Boolean as your answer, store it as a "1" (string) with AnswerType "Bool" or something like that. If you really wanted to get tight, you could create an "AnswerType" table to enforce referential integrity on the allowable types. Granted it's not "Codd", but from a pragmatic standpoint you'd save yourself a lot of headaches. Just wrapper it with a function or stored proc that will decode it for display purposes, and another to cast the type into a string for storage. Good luck, it looks good so far!