Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
outatime
Starting Member
2 Posts |
 Posted - 2009-03-10 : 12:47:26
|
| I have a Survey database with the following related tables:Surveys -> QuestionGroups -> Questions -> AnswersWhich of the following is a better design for the primary keys?A) Use composite keys where the keys are as follows:Surveys -> SurveyIDQuestionGroups -> SurveyID & QuestionGroupNumberQuestions -> SurveyID & QuestionGroupNumber & QuestionLetterAnswers -> SurveyID & QuestionGroupNumber & QuestionLetter & AnswerOrdinalB) Use a single unique ID and foreign key in each table:Surveys -> SurveyIDQuestionGroups -> fk_SurveyID & pk_QuestionGroupIDQuestions -> fk_QuestionGroupID & pk_QuestionIDAnswers -> fk_QuestionID & pk_AnswerIDAny thoughts will be appreciated! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-10 : 13:05:53
|
| What is relationships(Cardinality) between tables? Also include necessary attributes that goes with tables . |
 |
|
|
outatime
Starting Member
2 Posts |
Posted - 2009-03-10 : 17:04:10
|
| Surveys : QuestionGroups1:NQuestionGroups : Questions1:NQuestions : Answers1:NSurveysSurveyIDSurveyDescription...QuestionGroupsQuestionGroupNumberQuestionGroupTitle...QuestionsQuestionLetterQuestionText...AnswersAnswerOrdinalAnswerText...So I could insert a single primary key column in each table or use a composite key. For instance the primary key for the Answers table could be an AnswerID column, or a SurveyID column, a QuestionGroupNumber column, a QuestionLetter column, and the AnswerOrdinal column.I'm just not sure what is the best practice for this relationship. A composite key could be quite handy for reporting. A single answer key is quite handy for databinding to web controls... |
|
|
|
|
|
|
Nested Relationship Primary Key Design?