What's the best way to deal with the 8 different SQL questions below.
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.
Question #7 is a curve ball, because it potentially changes the answers to all the other questions.
Criteria
- Shouldn't require n+1 queries. Multiple queries are okay, but if every row returned requires an additional query, it's not scalable.
- 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.
- Retrieving a count on an object shouldn't trigger another SQL query.
- Shouldn't have to add a database column via denormalization if SQL allows me to aggregate the data
- Would a NOSQL solution, such as MongoDB or CouchDB, be better suited to answer all the questions below?
Database Schema
Students ------- ID Name Courses ----- ID Name Grade Enrollments ---------- ID Student_ID Course_ID
ActiveRecord Models
class Course < ActiveRecord::Base
has_many :enrollments
has_many :students, :through=>:enrollments
end
class Enrollment < ActiveRecord::Base
belongs_to :student
belongs_to :course
end
class Student < ActiveRecord::Base
has_many :enrollments
has_many :courses, :through => :enrollments
end
Questions
1) Retrieve all students in the 9th Grade Math Course
SQL
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'
Solution
This one is simple. ActiveRecord handles this well
c = Course.where(:grade=>9).where(:name=>'Math').first
c.students
2) Retrieve all Courses taken by John
SQL
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'
Solution
Again, simple.
s = Student.where(:name=>'John').first
s.courses
3) Retrieve all 9th grade Courses along with the number of students taking the course (but don't retrieve the students)
SQL
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
Solution
Counter Cache will work nicely here.
class AddCounters < ActiveRecord::Migration
def up
add_column :students, :courses_count, :integer, :default=>0
add_column :courses, :students_count, :integer, :default=>0
Student.reset_column_information
Student.all.each do |s|
Student.update_counters s.id, :courses_count => s.courses.length
end
Course.reset_column_information
Course.all.each do |c|
Course.update_counters c.id, :students_count => c.students.length
end
end
def down
remove_column :students, :courses_count
remove_column :courses, :students_count
end
end
ActiveRecord
Course.where(:grade=>9).each do |c|
puts "#{c.name} - #{c.students.size}"
end
4) Retrieve all students taking at least three 11th Grade Courses, more than one 10th Grade Courses, and no 9th grade courses
NO Solution
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.
Slow Solution
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)
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 <= 3 && courses_10 > 1 && courses_9 == 0
matching_students << s
end
end
return matching_students
5) Retrieve all students who are taking more than one math course query)
SQL
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
Or
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
NO Solution
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.
Slow Solution
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 << s if add_student
end
6) Retrieve all students who are taking a Math And Science course
SQL
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'
NO Solution
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.
Slow Solution
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 << s if has_math_course && has_science_course
end
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.
Solution
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?
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. 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?
