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 |
|
TurdSpatulaWarrior
Starting Member
36 Posts |
 Posted - 2004-01-06 : 16:59:46
|
Greetings. I am starting on a project that, in a nut shell, entails setting up dynamic surveys. The survey won't change too often, and will be having between 20 - 30 questions. Additionally, according to our client, this project may receive some heavy heavy traffic in the future, and as such needs to function FAST. A majority of the questions can have multiple answers. As a result, checkboxes are being used so that someone can choose more than one different applicable response. I'm trying to sort out how I'm going to get this all to work using relational data in SQL. This part directly below is pretty cut and dry (the questions and answer tables): Questions Table -QuestionID intQuestion varchar(65)Answers Table -AnswerID intQuestion int (corresponds to Questions table ID)Answer varchar(25) Here's the options I came up with (and I'm still trying to think of) for storing the answers for each user so that they can be editable, searchable, displayable, etc.: Option 1 User Answers TableUserID intQuestion intAnswer int The down side of this approach is that setting up the interface and programming to edit answers is going to be a pain. It probably would work best to delete all user answers, then re-insert on the processing of an edit, which might not help in keeping clean table indexes and is going to be lots of SQL executing (possibly 50 or so inserts if someone chooses many answers). However, searching and displaying should be fairly speedy since everything is going to be numeric. Option 2 User Answers TableUserID intAnswer1 varchar(50)Answer2 varchar(50)(etc etc, one column for each answer) In this method, each question would have it's own answer column. The answers would then be stored as delimited data, for example |45|76|80|99|, so that when searching you would do a LIKE searching such as SELECT X FROM Y WHERE (Z LIKE '%|76|%'). The downside is that if there are many many users, searches, etc. going on, running a bunch of LIKE's on varchar fields is NOT going to be speedy at all. Updating would be easier however, as it would only require an update of one row. Any other ideas, questions, or comments? |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-07 : 00:39:45
|
| Think the 1st ("normalized") plan is better. |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-01-07 : 02:01:48
|
| I agree with Stoad, the normalized solution will save you a lot of trouble in the long run. Delete and Insert will be quick enough, and dont worry about 50 inserts - you could always pass the answers as a Comma Delimited String to the Stored Proc, and let the SP worry about splitting it into 50 values and doing the corresponding inserts. Certainly beats doing 50 inserts from the client (think network traffic). You do have a point there about page splits occurring on indexes, but unless you have thousands of users, you really don't have much to worry about there.What are you doing your front-end in? I am sure you could easily do the front-end bit with a little smart logic and not too much code.OS |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-01-07 : 02:27:46
|
| I've done this exact thing before. The normalized way is the way to go. Dealing with the string manipulation sucks to develop, and REALLLY REALLY sucks when you have to revist this project down the road. I did one project with some string parsing stuff like you suggest and then I did the normalized way. When I had to go back and maintain the string parsing way, I wanted to re-write the whole thing. :)One big advantage to the normailzed way is if your survey ever gets melded into a "test" type thing, you can really easily say "This answer is the right answer for this question. if the user answered this question with this answer, it's correct." Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
TurdSpatulaWarrior
Starting Member
36 Posts |
Posted - 2004-01-07 : 09:15:37
|
| Thank you all for your constructive feedback. I tried searching some on this, but didn't have too much success (what in the heck do I search for this topic?!?!).There will potentially be well into the thousands of users on this, which is why I was taking the indexes getting chopped up into consideration. However, it might not be too bad if I schedule some periodic DBCC DBREINDEX's during off-peak hours. This is a web application that I am developing for a client in .NET v1.1. This survey is part of a user profile that will need to be searchable, editable, etc. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-01-07 : 10:17:03
|
| The search thing is just my sig. That's not directed at you directly.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
TurdSpatulaWarrior
Starting Member
36 Posts |
Posted - 2004-01-07 : 13:46:17
|
| O ok. My bad, sorry for any confusion. |
|
|
|
|
|
|
|
|
Small Project Architecture