Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Nested Relationship Primary Key Design? Expand / Collapse
Author
Message
Posted Wednesday, March 18, 2009 12:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 27, 2010 11:57 AM
Points: 8, Visits: 30
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!
Post #678804
Posted Wednesday, March 18, 2009 1:32 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:50 AM
Points: 3,109, Visits: 11,515
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.

Table			Keys
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

Post #678853
Posted Wednesday, March 18, 2009 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 27, 2010 11:57 AM
Points: 8, Visits: 30
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...
Post #678907
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse