database design query

[speedy]

Hi,
I am creating an website which will allow users to create their own
surveys.the surveys they create are then stored in a database, so that
other people using the website can answer the survey and their answered
surveys will also be stored in the database so that the person who
originally created it can view the responses.
Im having some problems deciding on the database design and just
wondering if anyone can help pls
The part im stuck on is how to create tables to store the completed
surveys..i already have tables to store the survey title,questions and
the answer options for each question
eg whats ur fave colour?
answer options could be red,yellow or green etc..
Now how would i store the answers eg if the user picks red,should this
be a completely seperate table?
really appreciate any help with this as ive been struggling with it for
a while. all suggestions welcome
thanks

[pietlinden]

look for an article/discussion that Keri Hardwick participated in like
1998 and mentions the word "survey" in either the subject or the
content of the message. she explains how to do it there - built
specifically for a web page etc.

[pietlinden]

From: "Keri Hardwick" <[e-mail]> - Find messages
by this author
Date: Fri, 05 Oct 2001 18:34:01 GMT
Local: Fri,Oct 5 2001 2:34 pm
Subject: Re: Questionnaire application (or data model)
Reply to Author | Forward | Print | View Thread | Show original |
Report Abuse

You will save yourself a lot of grief down the road if the table to
store
answers looks more like this:

SurveyId
QuestionId
Answer

rather than this:

SurveyId
Q1
Q2
Q3
Q4
....

Keri

"Nelson Fleet" <[e-mail]> wrote in message

news:86**************************@posting.google.c om...

I'd read the entire thread if I were you. A few interesting ideas in
there. And Keri's advice is right on the money. Nice thing about her
design is that it's so easy to add questions and summarize your
results, it's just silly. and if you can write ASP or something like
it, you can basically create a single page (or maybe two pages) and
just use the ASP to read the questions from your DB, create your page
on the fly, and write the answers back to a different table...

[speedy]

Hi
thanks for the reply,ive had a look thru the entire thread and its
helped a lot
cheers :-)