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

Carolyn Jewel Carolyn.Jewel at LEGACYNET.COM
Mon Nov 1 08:31:30 CST 2004


-----Original Message-----
From: ibura group [mailto:iburagroup at gmail.com] 
Sent: Thursday October 28, 2004 5:58 AM
Subject: [thelist] QUESTION: Asp.net and DB design issue

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

Can I answer - neither?  Method one sounds messy and prone to data
problems.  You will have a hard time writing queries to report on your
data. Method 2 sounds incomplete. However, there isn't really enough
information here to suggest a design that works well for the intended
purpose (which is?) My shot in the dark suggestion is pretty
traditional, build look up tables for values such as eye color, hair
color, education etc, with base tables such as one to define your
various forms (but I'm kind of unclear on why you'd be doing that) and
then the appropriate middle tables. If scalability counts, then you
really have to normalize your db design from the get-go and method 1
fails horribly at that. You can't build a good database without defining
your business rules, you need to ask and answer questions like: What
does your application actually do? Why are you asking these questions of
users and what will you (and your users) do with the answers? That
painful question and answer process is the only way to arrive at a
design that scales. 

Your db person should be asking those picky, irritating questions and
I'd be worried if he/she is not.

Carolyn J.


More information about the thelist mailing list