Nested Relationship Primary Key Design?

  • 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!

  • 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

  • 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...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply