Data design question - newbie

Hi all,

I have no idea if there is a lot of traffic going on but I thought I would try
to get a question answered.

Forgive me if the question is a bit long-winded as well as my ignorance in db design...

I have an application where users answer an extended questionaire. The questions are dynamically generated based on certain rules that are applied to previous answers. The end result is that there is no uniformity on what questions a user will get presented with or what questions a user will answer. Some users might get ten questions - some might get 50.

Both the questions and possible answers (we call them answerfields) are represented in the database. There is a one to many relationship between questions and answers.

For the most part there is only one answerfield per question. For instance, (not real examples):

for the question: How many times have you seen 'Batman'?

There would be a single answerfield called 'batman_views' that expects an number of some kind

Sometimes however there are more than one answerfield per question

for the question: which Batman movies have you seen?

there would be muliple answerfields called 'batman' , 'batman returns', 'batman on vacation', and 'batman returns from vacation' each expecting a boolean response.

The relationship between quesions and answerfields is used so that we can generate the questionaire.

My quesion is: how do I model the responses? I was thinking of something like the following:

A table that would have a column for the unique user_id, a column the answerfield_id, and then a third column to hold the response.

So if my answfield table looked like this

answerfield_id  answerfield_name        answerfield_type
1               batman                  number
2               batman returns          boolean
3               batman on vacation      boolean
4               batman returns from va  boolean

Then this result table

user_id answerfield_id response

1       1               15
1       2               true
1       3               true

Would represent a user had seen 'Batman' 15 times and had seen 'batman' and 'batman on vacation'.

Obviously the problem with storing the results in this manner is that there is no way to check the integrity of 'response' field. I would have to store them all as strings. So if something messed up I could easily have a situation where it looked like a user had seen Batman true times or enjoyed the '15' batman movies. This seems very very bad.

What am I missing?

If this is not the place for this type of question please let me know

Thanks in advance
Rob

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net