design question - newbie - Mailing list pgsql-general
From | rob@benefitscheckup.org |
---|---|
Subject | design question - newbie |
Date | |
Msg-id | aieobf$c8d$1@news.netmar.com Whole thread Raw |
Responses |
Re: design question - newbie
(Richard Huxton <dev@archonet.com>)
|
List | pgsql-general |
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
pgsql-general by date: