Re: Data design question - newbie

Hi Rob,

First, I would recommend Fabian Pascal's recent book: _Practical Issues in Database Management: A Reference for the Thinking Practitioner_ (Addison-Wesley; ISBN: 0201485559)

I can think of several different conceptual models that would fit the description you gave. Each would lead to a different logical model and a different physical model. Some are probably better for your use than others. You might need different subtypes of answers. Or you might be able to rephrase your problem such that all answers are true/false. For instance, will your application really care about the exact number of times the person has seen Batman in a range from 0 to infinite? Or does your application only care about ranges of numbers. eg. 0, 1, 2-4, 5-10, 11-infinite. In this case, the subject need only choose among five mutually exclusive boolean answers. Your batman titles would have several independent boolean answers. In this case, the subject need choose all of the boolean answers that apply.

Or, perhaps, all of your answers are quantified. Boolean in the range [0..1] and Integer in the range (-infinite,infinite) etc.

Regardless, you seem to have at least two subtypes of questions, and Fabian's book covers the topic of subtypes of entities. It is an excellent book, and I highly recommend it.

Cheers,
Bob

P.S. When answers are true/false, you need only store the true ones and you can omit the boolean field. The closed world assumption allows us to infer that the remainder of answers are false.

<rob_at_benefitscheckup.org> wrote in message news:aienmk$bsc$1_at_news.netmar.com...
>
>
> 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
eb -----
> 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 Received on Sat Aug 03 2002 - 05:20:00 CEST