Nested Relationship Primary Key Design?

• outatime

  SSC-Addicted

  Points: 422

  More actions

  March 18, 2009 at 12:41 pm

  #384154

  I have a Survey database with the following related tables:

  Surveys -> QuestionGroups -> Questions -> Answers

  These are all nested 1 to many relationships. Which of the following is a suggested practice design for the primary keys?

  A) Use composite keys where the keys are as follows:

  Table -> Key

  Surveys -> SurveyID

  QuestionGroups -> SurveyID & QuestionGroupNumber

  Questions -> SurveyID & QuestionGroupNumber & QuestionLetter

  Answers -> SurveyID & QuestionGroupNumber & QuestionLetter & AnswerOrdinal

  B) Use a single unique ID and foreign key in each table:

  Table -> Key

  Surveys -> SurveyID

  QuestionGroups -> fk_SurveyID & pk_QuestionGroupID

  Questions -> fk_QuestionGroupID & pk_QuestionID

  Answers -> fk_QuestionID & pk_AnswerID

  Any thoughts will be appreciated!

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  March 18, 2009 at 1:32 pm

  #962219

  Consider this variation of B. It allows you the flexibility to use a QuestionGroup in multiple Surveys without having to re-enter the questions for each Survey.

  TableKeys

  Survey

  PK = SurveyID

  QuestionGroup

  PK = QuestionGroupID

  SurveyQuestionGroup

  PK = SurveyQuestionGroup,

  FK = SurveyID to Survey,

  FK = QuestionGroupID to QuestionGroup,

  AK = SurveyID,QuestionGroupID

  Question

  PK = QuestionID,

  FK = QuestionGroupID to QuestionGroup,

  AK = QuestionID,QuestionGroupID

  Answer

  PK = AnswerID, FK = QuestionID,

  FK = QuestionID,QuestionGroupID to Question,

  FK = SurveyID,QuestionGroupID to SurveyQuestionGroup

• outatime

  SSC-Addicted

  Points: 422

  More actions

  March 18, 2009 at 2:37 pm

  #962266

  Hey, that's pretty slick! Especially if you're re-using questions. However, in my Survey application each QuestionGroup will belong to exactly 1 Survey. So all "children" will have exactly one "parent".

  There is also a "Responses" table. When a response is inserted for situation "A" it will need the SurveyID, QuestionGroupNumber, QuestionLetter, and AnswerOrdinal. For situation "B" it will need only the AnswerID.

  "B" would be handy for binding to web controls, but "A" would be handy for reporting.

  Decisions, decisions...