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@newsone.net