Hi everybody

I need to work with a database design that drives me crazy!!

We send out forms to collect learner data and teacher data. The
database in wich it is stored was designed as follows: All of the
different tables have only 3 attributes in it: 1. The Question Number.
2. The Form Number. 3.Answer to question.
Number 1 and 2 are Primary Keys, but also foreign keys that lookup the
values of the question's text and on wich form of the question is.

The group who design these tables, say that this is how it is suppose
to be done beacause it is in Third Normal Form. But I say thay are just
lazy, cause they apply this design to every database they design (they
take the brain work out of designing). It does not matter whether it is
telephone numbers, tel codes, Learner Numbers, Addresses or anything
else: They stick to this 3 attribute table

I do agree with them that it is in 3NF but What do you actually call a
design like this? (except stupid ;-) ) . I need to take them officially
on, on paper, but I do not know what term to call this design!

Can somebody please help me

On 21.09.2006 16:03, hansie (AT) edu (DOT) fs.gov.za wrote:
Actually I don't understand why there are many tables with the same
schema. Usually this is an indication that one table (probably with an
added column) might be enough.

You did not tell what kinds of questionnaires these application do. Is
it multiple choice or free form? If these tables contain possible
answers for multiple choice questions then there seems to be an answer
number missing (you have multiple answers per question). If these
tables record actual answers then I would expect at least a user
identifier in there.

I guess there's tons of ways to do this but we do not have much
information about your application. Maybe we can come up with more info
once you provide more detail.

Kind regards

robert

It looks like you are designing a database to track questionnaires.
Questionnaire based designs are tough and there really isn't a good way to
do them.

One approach is the "wide-flat" model where you have one table to track all
the responses.

Responses table
- <Composit ID stuff to uniquely identify each row as belonging to a
particular questionnaire, respondent, etc.>
- Q1 (Answer to Question #1...)
- Q2
- Q3
-...
- Qn


This is OK, but only to a point. Some questionnaires may have 5 questions,
others 50.

The other approach, which seems to be where your company is going is the
more normalized approach:

Responses table
- <Composit ID stuff to uniquely identify each row as belonging to a
particular questionnaire, respondent, etc.>
- QuestionNumber
- Answer

The problem with this approach is determining the datatype for the Answer
column. Often questionnaires will have a mix of true/false, multiple choice,
textual, and numeric answers. When that's the case, the answers start to
look more like attributes of a single response instrument instead of a one
to many relationship.

In the end questionnaires are more suited to statistical packages than
databases.