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

[Peter Brunone (EasyListBox.com)]

	No doubt about it; the second approach will be more flexible.
With the first design, you'll have to create a new table every time you
have a new form, and a new field whenever you have a new question.  Your
queries will have to change as well.

	With the one-table approach, you may want to have another
reference table that lists formID and some details about each form (like
title, etc).

IANADBA, YMMV, yadda yadda yadda...

Peter

-----Original Message-----
From: thelist-bounces at lists.evolt.org On Behalf Of ibura group

Hello All,

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.

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 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

Can you please help explain the pros and cons of each design, or offer a
better design that will ensure scalability.

Thanks in advance.
AG
--