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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-06-16 : 08:45:05
|
| Chris writes "Scenario: Online Test with x questions drawn randomly from a pool of y questions. The random order must be duplicable for each student.Platform SQL Server 2005.Existing Tables (I can't alter these -- there's too much associated logic in other apps which I did not write)Students--------IDinfoQ1numQ2num...QXnumQ1num ... QXnum are populateed by a web app which shuffles the deck and assigns question IDs at registration time. This allows us to regenerate the exact historical exam at any time. (Like I said earlier, NOT my design)Questions---------QIDQTextQChoiceAQChoiceB...What I am doing currently O(N^2) -- could make in O(NLogN) with a binary search!SELECT * from Questions, Students WHERE Students.ID = @passedParam AND Q1num = QID OR Q2num = QID ...This query is generated in a for loop on the webserver, and isn't hard to do. It yields a complete question set but not in any particular order. Currently I am then searching through the DataSet for Q*Num and populating a Questions array in the correct order.What I want to do is retrieve a DataSet that includes the question ordinal which can then be ordered and I can iterate over the set and get the questions in the correct order.Sample DataStudents:ID Info Q1Num Q2Num ....100 **** 27 18Questions:QID QText ... 9 What is your name?18 What is your favourite colour?27 What is the capital of Assyria?36 What is the air speed velocity of a swallow?Desired Query Result (for a particular student)Ordinal QID QText ... 1 27 What is the capital of Assyria?2 18 What is your favourite colour?My solution would have been to re-design the table structure making a many to many relationship with an intermediate table consisting of StudentID, Ordinal, and QID to store the random numbers as rows (thus stripping these columns out of the students table), but I am not allowed to do this.Any suggestions? Thanks in advance,Chris" |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-06-16 : 10:15:56
|
| I don't think there's a pretty solution. Maybe you could go half-way to your solution, and create views which are normalised versions of the existing denormalised tables. You could then work from the views as if they were your normalised table structure.You'll just need to use a lot of unions in your views!Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
 |
|
|
|
|
|
Transposition issue