Hi,

I need to create a web fronted database to collate info from students and put them into a db.
I have my questions, some Y/N answers and some text.
So far I know I will need these tables:

Table: Survey
SurveyID
SurveyName

Table: Questions
QuestionID
QuestionText
SurveyID

Table: Answers
AnswerID
QuestionID
AnswerYN
UserID

Table: Users
UserID
Username

I'm just not sure what the relationships will be. Which ones are are one-many and such?

Any help would be greatly appreciated.

can a user answer the same question more than once? if not you should remove Answers.AnswerID column, and make QuestionID,UserID a composite PK of that table. Having a separate AnswerID column would allow dupes on the QuestionID,UserID combination, but perhaps that's appropriate. depends on your reqs.

can questions be shared between surveys? if so you might think about a SurveyQuestion table that sits between Survey and Question. otherwise you'd have the same question duped in the Question table.

you might also add a datetime to Answers so you know when a particular user answered a question.

all questions have only true/false answers? my life is not that simple...

Okay, truth is I am stuck.

I have 19 questions in total on one survey.
I want to store information about the respondant, but just the name.
I have some questions where there is a text response, a yes/no response and a multiple choice answer.

Please can someone help me with the layout of this db?

have a look at this design -- http://databaseanswers.org/data_mode...plex/index.htm