<p>Say I have a Student table, it's got an int ID.  I have a fixed set of 10 multiple choice questions with 5 possible answers.  I have a normalized answer table that has the question id, the Student.answer (1-5) and the Student.ID</p>

<p>I'm trying to write a single query that will return all scores over a certain pecentage.  To this end I wrote a simple UDF that accepts the Student.answers and the correct answer, so it has 20 parameters.</p>

<p>I'm starting to wonder if it's better to denormalize the answer table, bring it into my applcation and let my application do the scoring.</p>

<p>Anyone ever tackle something like this and have insight? </p>

[{'answer_id': 22421, 'author': 'Bryan Roth', 'author_id': 299, 'author_profile': 'https://Stackoverflow.com/users/299', 'pm_score': 1, 'selected': False, 'text': '<p>I would probably leave it up to your application to perform the scoring.  Check out <a href="http://www.codinghorror.com/blog/archives/001152.html" rel="nofollow noreferrer">Maybe Normalizing Isn\'t Normal</a> by Jeff Atwood.</p>\n'}, {'answer_id': 22424, 'author': 'palehorse', 'author_id': 312, 'author_profile': 'https://Stackoverflow.com/users/312', 'pm_score': 0, 'selected': False, 'text': '<p>The architecture you are talking about could become very cumbersome in the long run, and if you need to change the questions it means more changes to the UDF you are using.</p>\n\n<p>I would think you could probably do your analysis in code without necessarily de-normalizing your database.  De-normalization could also lend to inflexibility, or at least added expense to update, down the road.</p>\n'}, {'answer_id': 22426, 'author': 'Shawn', 'author_id': 26, 'author_profile': 'https://Stackoverflow.com/users/26', 'pm_score': 0, 'selected': False, 'text': "<p>No way, you definitely want to keep it normalized. It's not even that hard of a query.</p>\n\n<p>Basically, you want to left join the students correct answers with the total answers for that question, and do a count. This will give you the percent correct. Do that for each student, and put the minimum percent correct in a where clause.</p>\n"}, {'answer_id': 22427, 'author': 'Eric Z Beard', 'author_id': 1219, 'author_profile': 'https://Stackoverflow.com/users/1219', 'pm_score': 0, 'selected': False, 'text': "<p>Denormalization is generally considered a last resort.  The problem seems very similar to survey applications, which are very common.  Without seeing your data model, it's difficult to propose a solution, but I will say that it is definitely possible.  I'm wondering why you need 20 parameters to that function?</p>\n\n<p>A relational set-based solution will be simpler and faster in most cases.</p>\n"}, {'answer_id': 22448, 'author': 'Dave Ward', 'author_id': 60, 'author_profile': 'https://Stackoverflow.com/users/60', 'pm_score': 3, 'selected': True, 'text': '<p>If I understand your schema and question correctly, how about something like this:</p>\n\n<pre><code>select student_name, score\nfrom students\n  join (select student_answers.student_id, count(*) as score\n        from student_answers, answer_key\n        group by student_id\n        where student_answers.question_id = answer_key.question_id\n          and student_answers.answer = answer_key.answer)\n  as student_scores on students.student_id = student_scores.student_id\nwhere score &gt;= 7\norder by score, student_name\n</code></pre>\n\n<p>That should select the students with a score of 7 or more, for example.  Just adjust the where clause for your purposes.</p>\n'}, {'answer_id': 22494, 'author': 'Stu', 'author_id': 414, 'author_profile': 'https://Stackoverflow.com/users/414', 'pm_score': 0, 'selected': False, 'text': '<p>This query should be quite easy... assuming you have the correct answer stored in the question table. You do have the correct answer stored in the question table, right?</p>\n'}]
['https://Stackoverflow.com/questions/22417', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1975/']