RE: Database Design

From: MeanOldDBA (MeanOldDBA_at_discussions.microsoft.com)
Date: 09/15/04 

Date: Wed, 15 Sep 2004 02:45:03 -0700

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

Relevant Pages

  • RE: Database Design
    ... > what your business needs are. ... > AnswerKey ... > database design is making sure the design is strong, ... how do i created a table for the survey data. ...
    (microsoft.public.sqlserver.server)
  • Re: Design question - best practice to store survey questions
    ... Your response to the design of tblSurveys ... Re no design for the answers tables: Well, ... So above the line between SURVEY and QUESTION ...
    (comp.databases)
  • Re: Data entry forms and/or subforms
    ... > entered are survey responses from high school students. ... > entering the data into a single table with a student id serving as the ... And you are now running into problems as a result of this table design setup. ... > once I enter a student id, all of the subsequent data on the subforms ...
    (microsoft.public.access.forms)
  • Re: Web Survey database design
    ... > I am a developer and I have a problem trying to design a system to ... > manage data coming from web surveys. ... > I am focusing here only on the tablethat will hold the survey data. ... other SQL Server data type except text, ...
    (comp.databases.ms-sqlserver)
  • Re: Adding based on YES/NO answers in form
    ... The better way is to go from a flat, unnormalized design where all of your ... QuestionOptions for listing possible choices to a given question (which ... Survey (this would probably be analgous to the table you are currently using ... > If answered Temp YES Total Points +8 if NO Total Points 0> ...
    (microsoft.public.access.formscoding)