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 |
|
wgpubs
Yak Posting Veteran
67 Posts |
 Posted - 2004-10-01 : 15:55:17
|
| Hey ya'll,Been tasked with developing a scalable 150 question survey system from the ground up. Back-end needs will be SQL 2K ... front-end will be ASP.NET. Roughly expecting 20,000 participants to take it ... looking for any advice on structuring the application (database specifically) so as to make the system as scalable as possible. In particular, I'm looking for the best way to maintain participant answers.Any advice? tips?Thanks - wg |
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2004-10-01 : 16:39:21
|
| Whatever the choice... you can benefit from asp.net page caching here to reduce the trips to the database. Without having more details about your survey its hard to tell you. Things that would help us help youIs the survey multiple choice or can the user give any answer? If multiple choice will there be an other field to allow input?Will you be tracking actual user data as well or just the responses to the answers. ( Such as will you need reports on individual users of teh system or just talleys of the answers ). |
 |
|
|
wgpubs
Yak Posting Veteran
67 Posts |
Posted - 2004-10-01 : 18:05:49
|
| To answer your questions:1. The survey has multiple question types(yes/no, multiple choice, free-formed text)2. Yes, some yes/no and multiple choice will allow user to add a comment.3. Need to track both answers and textual comments.My biggest concer is how to maintain a respondents answers to each question. With 20k participants .... want to make the system as scalable and flexible (ability to add/reduce question list, persist answers, etc...) as possible. |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2004-10-01 : 20:46:56
|
| [code]create table Questions( QuestionID int identity(1,1) primary key not null, Question varchar(1000), MultipleChoice bit, -- multiple choice? QuestionNumber int -- Insert trigger for update, insert and delete to reorder this column)-- multiple choice answerscreate table answers( answerid int identity(1,1) primary key not null, relQuestionID int, -- points to the question id answer varchar(1000))-- results from users-- Heres your table that will grow to-- 20,000 * number of questions-- so if you have 150 questions then your looking-- at 20,000 * 150 = 3 million rows :), however-- your saving lots of space by using the answers table.create table UserAnswers( relAnswerID int null, -- the relation to the answer id, if null manualAnswer should be considered the answer manualanswer varchar(2000), -- stores manually inputed answers or other fields username varchar(100), -- however you plan to track the user )[/code]Consider this too... you could also store a comma delimited list of multiple choice answers in your users table and only store comments in the UserAnswers table.. Personally I dont like that though its kindof a hack and may come back to haunt you. Of course index your foreign keys. |
|
|
|
|
|
|
|
|
Need to design scalable survey from scratch - HELP