[thelist] QUESTION: Asp.net and DB design issue

[John.Brooking at sappi.com]

> I am working with a contractor on a project and I have a 
> question I am hoping some of you all can help me with.  I am 
> building an asp.net application that asks the users a number 
> of personal questions(ex: hair color, eye color, education, 
> occupation etc).  In total I have these questions broken up 
> into 7 forms.

It sounds to me like you want a data-driven solution to selecting and
displaying the questions for each form, then recording the answers. The most
"normalized" relational solution would be something like:

   USERS
      UserID	Primary Key
      Last Name
      First Name
      ...
      
   FORMS
      FormID	Primary Key
      Sequence	Unique Not Null
      Title	Not Null
      ...
      
   QUESTIONS
      QuestionID		Primary Key
      FormID		Foreign Key to FORMS
      Sequence		Unique Not Null
      QuestionText	Not Null
      ...
            
   ANSWERS
      QuestionID	FK to QUESTIONS
      UserID	FK to USERS
      Answer

I would make all primary keys auto-generated sequential numbers. Also feel
free to add additional information into each table. In particular, if you
wanted to get tricky, you could add an answer type to each question:
free-form text, select drop-down or radio button (with list of choices),
checkbox, etc. This structure should give you the flexibility you will need
to generate each form using identical code, record the answers, and report
on them afterwards.

> Can you please help explain the pros and cons of each 
> design, or offer a better design that will ensure 
> scalability.
>
> The person designing the DB has the following design in mind:
> - 7 tables, one for each form
> - Each table has a field for each of the questions on that 
> page, some as high as 20 field to reflect each question, 
> with a user id in each record as well.

I see no need to have a separate table for each form. They all have pretty
much the same information, so one table with a form identifier on it is more
maintainable, and makes more sense conceptually. Also, you don't want the
user ID and answer on the same record as the question, as there will be many
answers to each question.

> I was also told I should use the following:
> - 1 table for all of the questions
> - Each record would have a userID, a formID, a questionID, and an answer

I think this person must mean a table of questions, and a separate table of
answers, for the reason given above. The only difference I have with this is
if you use a unique ID for each question in the Questions table, regardless
of which form it is on, you don't need the FormID in the Answers table. Then
if you decide to move a question to another form (maybe unlikely, but say
you do), you don't need to change all the references to the form in the
Answers table. Conceptually, what form the question is on isn't important to
the answer. (The person suggesting this probably was thinking of the
QuestionID being relative to each Form, so they form the primary key
together. That would work too, but I think my way is better, because it
keeps them independent. The form that a question is on is more of a property
of the question than an integral part of its identity, in my opinion.)

For general background reading, you might want to google for articles on
some combination of terms like "Relational Database Theory", "Database
Normalization", "Normal Form", or "E.F. Codd" (the recognized originator of
said theory).

Have fun!

- John
-- 

This message may contain information which is private, privileged or
confidential and is intended solely for the use of the individual or entity
named in the message. If you are not the intended recipient of this message,
please notify the sender thereof and destroy / delete the message. Neither
the sender nor Sappi Limited (including its subsidiaries and associated
companies) shall incur any liability resulting directly or indirectly from
accessing any of the attached files which may contain a virus or the like.