Database Design

Hi,
I'm a newbie, how do i created a table for the survey data.
Given the questions for each user:
Do you Smoke ? Answer Yes
If Yes, How many times Answer 3
Do you take alcohol? Answer No
How will i create a table for this.
Do i do like this
UserName Do_You_Smoke How_Many_Times Alchohol
User1 yes 3 No
I want to create a report from this survey and Pull up the data for each
user on the form again for modification
Please Help
There are a lot of possible answers to this question. It really depends on
what your business needs are. I would assume you're going to want to change
the survey often, so you might want to consider something like this:
Question
======
QuestionKey
Question
BeginDate
EndDate
Answer (You might actually want to have several of these, depending on
complexity)
=====
AnswerKey
Answer
BeginDate
EndDate
QuestionAnswerMatrix (Match available answers where needed.)==============
QuestionKey
AnswerKey (AnswerKey could be blank for free text answers if you have those.)
BeginDate
EndDate
User
====
UserKey
UserName
etc, etc, etc
UserAnswers
=========
UserKey
QuestionAnswerKey
SurveyDate
This should at least give you some ideas. The most important thing in
database design is making sure the design is strong, normalized and yet meets
the needs of the business. If you haven't even asked the questions, there's
no way to design well. Based on the design above though, you should be able
to create stored procedures that easily report on and modify the data for the
surveys, as well as track historical survey "template" changes.
"Givosky" wrote:

> Hi,
> I'm a newbie, how do i created a table for the survey data.
> Given the questions for each user:
> Do you Smoke ? Answer Yes
> If Yes, How many times Answer 3
> Do you take alcohol? Answer No
> How will i create a table for this.
> Do i do like this
> UserName Do_You_Smoke How_Many_Times Alchohol
> User1 yes 3 No
> I want to create a report from this survey and Pull up the data for each
> user on the form again for modification
> Please Help
|||If your intention is actually to record information about each user then
something like this might be appropriate:
CREATE TABLE Users (username VARCHAR(30) NOT NULL, smokes_per_day INTEGER
NOT NULL CHECK (cigarettes_per_day>=0), alcohol_consumption INTEGER NOT NULL
.... etc)
I'm guessing that the Yes/No answers are redundant. They would just drive
the presentation in the GUI rather than be actual useful information (for
example cigarettes_per_day = 0 implies "I don't smoke"). On the other hand if
you need something more generic to handle many different types of survey you
might take a quite different approach.
David Portas
SQL Server MVP
|||Thanx for that useful information
"MeanOldDBA" wrote:
[vbcol=seagreen]
> There are a lot of possible answers to this question. It really depends on
> what your business needs are. I would assume you're going to want to change
> the survey often, so you might want to consider something like this:
> Question
> ======
> QuestionKey
> Question
> BeginDate
> EndDate
> Answer (You might actually want to have several of these, depending on
> complexity)
> =====
> AnswerKey
> Answer
> BeginDate
> EndDate
> QuestionAnswerMatrix (Match available answers where needed.)==============
> QuestionKey
> AnswerKey (AnswerKey could be blank for free text answers if you have those.)
> BeginDate
> EndDate
> User
> ====
> UserKey
> UserName
> etc, etc, etc
> UserAnswers
> =========
> UserKey
> QuestionAnswerKey
> SurveyDate
> This should at least give you some ideas. The most important thing in
> database design is making sure the design is strong, normalized and yet meets
> the needs of the business. If you haven't even asked the questions, there's
> no way to design well. Based on the design above though, you should be able
> to create stored procedures that easily report on and modify the data for the
> surveys, as well as track historical survey "template" changes.
>
> "Givosky" wrote: