Correct Relationships

[goslincm]

Good morning, struggle to establish proper relationships.

tbl_survey

surveyID pk
surveyName

tbl_questions
questionID pk
surveyid fk
question

tbl_answers

answerID
surveyID
questionID
answer

Question: in my tbl_answers what should my primary key be? combination of the three ID fields?

 

[PHV]

Leslie, in the access microcosm they talk about append query.

 

[lespaul]

Well, Access may call it append, but it creates an INSERT query too! Bad Access!

Goslin,
In response to the order of adding the data, start with the tables that have no foreign keys.

Say you try to add information to the ResponderSurvey table when you haven't created the Survey information...you'll get a key violation because there is NO survey information in the table!

Personally I would do it like this:

Responders/Survey/Questions (in any order)
SurveyQuestions
ResponderSurveys
Responses

leslie

 

[BobRodes]

I've got one question about your design, lespaul. I'm not sure I see how you determine in your responses table which question on a survey is being responded to. Can you point that out?

 

[goslincm]

Could that be why when I start entering information into the tables, as Leslie suggested (the order), when I get to the ResponderSurvey table, I have no information in it. It has no primary key because nothing is in the response table to generate the respondersurveyid number.

 

[lespaul]

That's why having more than one set of eyes review things is a good idea....didn't notice I didn't have that in there. Perhaps adding the QuestionID to that table....or did you find another solution?

Leslie

 

[BobRodes]

Well, yes, I would add the questionID. But also, my feeling was that responses were to questions, and that questions were in surveys. It seems a simplification to me to relate responders to questions rather than surveys. We can relate surveys to questions and still know what responders took which surveys. So:

Responders 1 RespondersQuestions *
Questions 1 RespondersQuestions *
Surveys 1 Questions *

Adding in the SurveyQuestions table suggests that a responder can take the same survey twice, which this schema assumes not to be the case. So, I would add that in as a refactoring if required. However, I would eliminate the respondersurveys table and add, as you say, the questionid as a FK (or as part of a composite PK) to the respondersquestions table (your responses table, which is a better name but less illustrative of the associative entity it represents).

Bob

 

[goslincm]

Thanks so much for all the help everyone, it truly has been a learning experience. I have also done as Duane suggested and purchased a copy of SQL for Mere Mortals...