title: ActiveRecord - Denormalization Case Study
body: <p><strong>What's the best way to deal with the 8 different SQL questions below.</strong></p>
  
  <p>I have placed below a database schema, how it is represented in my Rails models, and seven questions for data I need to get out of my database.  Some questions I have answered, others I'm not sure of the best solution.</p>
  
  <p><strong>Question #7 is a curve ball, because it potentially changes the answers to all the other questions.</strong></p>
  
  <h1>Criteria</h1>
  
  <ol>
  <li>Shouldn't require n+1 queries.  Multiple queries are okay, but if every row returned requires an additional query, it's not scalable.</li>
  <li>Shouldn't require post-processing to filter results that SQL can do on its own.  For example, the answer to number five shouldn't be to pull ALL students from the data store, then remove those with no Courses.</li>
  <li>Retrieving a count on an object shouldn't trigger another SQL query.</li>
  <li>Shouldn't have to add a database column via denormalization if SQL allows me to aggregate the data </li>
  <li>Would a NOSQL solution, such as MongoDB or CouchDB, be better suited to answer all the questions below?</li>
  </ol>
  
  <h1>Database Schema</h1>
  
  <pre>
  Students
  -------
  ID
  Name
  
  Courses
  -----
  ID
  Name
  Grade
  
  Enrollments
  ----------
  ID
  Student_ID
  Course_ID
  </pre>
  
  <h1>ActiveRecord Models</h1>
  
  <pre><code>
  class Course &lt; ActiveRecord::Base
    has_many :enrollments
    has_many :students, :through=&gt;:enrollments
  end
  class Enrollment &lt; ActiveRecord::Base
    belongs_to :student
    belongs_to :course
  end
  class Student &lt; ActiveRecord::Base
    has_many :enrollments
    has_many :courses, :through =&gt; :enrollments
  end
  </code></pre>
  
  <h1>Questions</h1>
  
  <h2>1) Retrieve all students in the 9th Grade Math Course</h2>
  
  <h3>SQL</h3>
  
  <pre><code>
  SELECT s.* FROM Students s
  LEFT JOIN Enrollments e on e.student_id = s.id
  LEFT JOIN Courses c on e.course_id = c.id
  WHERE c.grade = 9 AND c.name = 'Math'
  </code></pre>
  
  <h3>Solution</h3>
  
  <p>This one is simple. ActiveRecord handles this well</p>
  
  <pre><code>
  c = Course.where(:grade=>9).where(:name=>'Math').first
  c.students
  </code></pre>
  
  <h2>2) Retrieve all Courses taken by John</h2>
  
  <h3>SQL</h3>
  
  <pre><code>
  SELECT c.* FROM Courses c
  LEFT JOIN Enrollments e on c.id = e.course_id
  LEFT JOIN Students s on e.student_id = s.id
  WHERE s.name = 'John'
  </code></pre>
  
  <h3>Solution</h3>
  
  <p>Again, simple.</p>
  
  <pre><code>
  s = Student.where(:name=>'John').first
  s.courses
  </code></pre>
  
  <h2>3) Retrieve all 9th grade Courses along with the number of students taking the course (but don't retrieve the students)</h2>
  
  <h3>SQL</h3>
  
  <pre><code>
  SELECT c.*, count(e.student_id) FROM Courses C
  LEFT JOIN Enrollments e on c.id = e.course_id
  WHERE c.grade = 9 GROUP BY c.id
  </code></pre>
  
  <h3>Solution</h3>
  
  <p>Counter Cache will work nicely here.</p>
  
  <pre>
  class AddCounters &lt; ActiveRecord::Migration
    def up
      add_column :students, :courses_count, :integer, :default=&gt;0
      add_column :courses, :students_count, :integer, :default=&gt;0
      Student.reset_column_information
      Student.all.each do |s|
        Student.update_counters s.id, :courses_count =&gt; s.courses.length
      end
      Course.reset_column_information
      Course.all.each do |c|
        Course.update_counters c.id, :students_count =&gt; c.students.length
      end
    end
  
    def down
      remove_column :students, :courses_count
      remove_column :courses, :students_count
    end
  end
  </pre>
  
  <p>ActiveRecord</p>
  
  <pre>
  Course.where(:grade=>9).each do |c|
    puts "#{c.name} - #{c.students.size}"
  end
  </pre>
  
  <h2>4) Retrieve all students taking at least three 11th Grade Courses, more than one 10th Grade Courses, and no 9th grade courses</h2>
  
  <h3>NO Solution</h3>
  
  <p>Not sure of the best solution.  This would be VERY messy to do in SQL without keeping a counter cache for number of courses per grade level on each student.  I could add a hook to update this information myself.  I don't want to pull all students and courses and count them in post processing.</p>
  
  <h3>Slow Solution</h3>
  
  <p>The following solution produces a lot of queries.  Preloading the courses may not be possible.  (For example, the students are coming from the association on a course)</p>
  
  <pre><code>
  students = some_course.students
  matching_students = []
  students.each do |s|
    courses_9 = 0
    courses_10 = 0
    courses_11 = 0
    s.courses.each do |c|
      courses_9  += 1 if c.grade == 9
      courses_10 += 1 if c.grade == 10
      courses_11 += 1 if c.grade == 11
    end
    if courses_11 &lt;= 3 && courses_10 &gt; 1 && courses_9 == 0
      matching_students &lt;&lt; s
    end
  end
  return matching_students
  </code></pre>
  
  <h2>5) Retrieve all students who are taking more than one math course
  query)</h2>
  
  <h3>SQL</h3>
  
  <pre><code>
  SELECT s.*, count(e.course_id) as num_Courses FROM Students s
  INNER JOIN Enrollments e on s.id = e.student_id
  INNER JOIN Courses c on e.course_id = c.id AND c.name = 'Math'
  GROUP BY s.id HAVING num_Courses > 0
  </code></pre>
  
  <p>Or</p>
  
  <pre><code>
  SELECT DISTINCT s.* FROM Students s
  INNER JOIN Enrollments e_math_1 on e_math_1.student_id = s.id
  INNER JOIN Courses c_math_1 ON e_math_1.course_id = c_math_1.id AND c_math_1.name = 'Math'
  INNER JOIN Enrollments e_math_2 on e_math_2.student_id = s.id
  INNER JOIN Courses c_math_2 ON e_math_2.course_id = c_math_2.id AND c_math_2.name = 'Math'
  WHERE c_math_1.id != c_math_2.id
  </code></pre>
  
  <h3>NO Solution</h3>
  
  <p>Not sure of the best solution.  The tricky part to this is that the ActiveRecord (or NoSQL) solution can't retrieve all students, and looking at their courses afterwards, because that would be too slow.</p>
  
  <h3>Slow Solution</h3>
  
  <pre><code>
  students = SomeObject.students
  multiple_math_course_students = []
  students.each do |s|
    has_math_course = false
    add_student = false
    s.courses.each do |c|
      if c.name == 'Math'
        if has_math_course
          add_student = true
        else
          has_math_course = true
        end
      end
    end
    multiple_math_course_students &lt;&lt; s if add_student
  end
  </code></pre>
  
  <h2>6) Retrieve all students who are taking a Math And Science course</h2>
  
  <h3>SQL</h3>
  
  <pre><code>
  SELECT s.* FROM Students s
  INNER JOIN Enrollments e_math on e_math.student_id = s.id
  INNER JOIN Courses c_math ON e_math.course_id = c_math.id
  INNER JOIN Enrollments e_science on e_science.student_id = s.id
  INNER JOIN Courses c_science on e_science.course_id = c_science.id WHERE c_math.name = 'Math' AND c_science.name = 'Science'
  </code></pre>
  
  <h3>NO Solution</h3>
  
  <p>This involves joining to the same table (or in Rails, association) twice.  Is there a way to do this smoothly with ActiveRecord's AREL wrapper?  You could make a separate association for science classes and math classes, allowing you to do separate operations on each, but this won't work in the case of #7 below.</p>
  
  <h3>Slow Solution</h3>
  
  <pre><code>
  students = SomeObject.students
  math_and_science_students = []
  students.each do |s|
    has_math_course = false
    has_science_course = false
    s.courses.each do |c|
      has_math_course = true if c.name == 'Math'
      has_science_course = true if c.name == 'Science'
    end
    math_and_science_students &lt;&lt; s if has_math_course && has_science_course
  end
  </code></pre>
  
  <h2>7) The customer has stated that anytime a student is shown in the system, display a number next to the student that shows the highest grade level course they are taking.  For example, if Suzie is taking a 9th grade science course and a 10th grade math course, display a '10' next to Suzie.</h2>
  
  <h3>Solution</h3>
  
  <p>It would not be acceptable to query the database for every student record.  A page which displays 100 students would require 100 queries.  At this point, I want to denormalize the database by putting a flag in the student table with "highest level course".  Is this my best course of action?  Would it be better to use a different data store other than a relational database from the start?</p>
  
  <p><p><strong>Imagine that the customer asked for any arbitrary data to be shown as a badge: Highest Grade Level, Number of Math Courses Taken, Gold Badge if taking Math, Science and History all together, etc.</strong>  Should each of these cases be a call for denormalization of the database?  Should denormalized data be kept in the same relational database as normalized data?<p></p>