I'm trying to implement a simple Y/N type survey on our website using
Postgresql and pHp. I am having difficulty, though, getting the
database design straight. I've tried multiple scenarios, none of
which have satified my needs. Here's what I'm looking to do:
Each week, I'd like to have a simple survey where I ask a question and
have a simple For or Against option as the answer. Each survey may
have multiple questions. We already have our members only site
integrated with our membership database, so I can pull the userID from
there to insure that each person only votes once. My issue is I can't
think of the best design for the table structure. Here's what I've
tried.
Table: Survey
SurveyID
SurveyName
Table: Questions
QuestionID
QuestionText
SurveyID
Table: Answers
AnswerID
QuestionID
AnswerYN
UserID
Table: Users
UserID
Username
What I think this allows me to do, is have multiple surveys, with
multiple questions, with multiple answers. Of course, I will validate
that the user has not already submitted answers to the survey with a
session cookie when they initiate the response, so they will not be
able to vote twice. But, this design does not appear to do what I'm
looking for. I am trying to write the front end in Access so that
other people in my office can create surveys for our website, and I
will just change some code on the survey page to reflect the new
surveryID. When I try to write the front end, it just doesn't work
like I expect it to. Is there something I'm missing? Any help would
be greatly appreciated.
Thanks,
Dave