The only thing I think I would change is you don't need the surveyID in the SurveryAnswers table. Since you have the questionID, and the SurveyQuestions table ties back to the SurveyMaster table, you can join those tables (which you would most likely be doing any when displaying the results) to get what you need. It wont hurt to keep it, but my preference would be to get rid of it. You may also want to consider moving the selectable answers into a different table in case you need to add more than 4 possible answers.
It's good to see somebody think like me. I maintain a pretty large database of exams, questions, and student answers for my job (a public school system), and my db design is very similar to what your proposing. My boss tried to convince me to keep EVERYTHING in 1 table - exam name, all questions, and all student responses. I always call this type of design "spreadsheet data", and his argument is that if you are ultimately going to display it like a spreadsheet (since it will be displayed in a table like fashion), then you should store it like a spreadsheet. Luckily, I convinced him otherwise. Steve Les Mizzell wrote: > I'm building on online survey with multiple choice questions for a client. > Client will be able to add/edit surveys, add/edit questions, and get > stats once folks have taken the survey. > > I'm puzzling a little over the best way to collect answers. > > Tables are sorta like > > 1. SurveyMaster > a. surveyID > b. surveyTITLE > > 2. SurveyQuestions > a. surveyID > b. questionID > c. question > d through g - selectable answers > > There's not a set number of questions for any survey. The client could > enter 2 questions, or 50 questions, who knows (always four possible > answers though) - so I thinking that perhaps in the answer table every > single questions gets a row to itself? > > 3. SurveyAnswers > a. surveyID > b. questionID > c. questionANSWER > > This might make it easier to calculat stats. > > Or, would it be better to build an array like > questionID|questionANSWER and store all the answers from one user that > way. Will take a little more head scratching to quild stats from this > though (so it seems) > > How would any of you do it? Seems to be any number of ways. What might > be the most optimal? > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234209 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
