ActiveRecord - пример денормализации

Question

Какой лучший способ справиться с 8 различными вопросами SQL ниже.

Ниже я поместил схему базы данных, то, как она представлена ​​в моих моделях Rails, и семь вопросов о данных, которые мне нужно получить из моей базы данных. На некоторые вопросы я ответил, другие я не уверен в лучшем решении.

Вопрос № 7 - это кривая, потому что он потенциально меняет ответы на все остальные вопросы.

критерии

1. Не должно требовать n+1 запросов. С несколькими запросами все в порядке, но если для каждой возвращаемой строки требуется дополнительный запрос, он не масштабируется.
2. Не нужно требовать постобработки для фильтрации результатов, которые SQL может выполнять самостоятельно. Например, ответом на номер пять не должно быть извлечение ВСЕХ студентов из хранилища данных, а затем удаление тех, у кого нет курсов.
3. Получение счетчика на объекте не должно вызывать другой запрос SQL.
4. Не нужно добавлять столбец базы данных с помощью денормализации, если SQL позволяет мне объединять данные
5. Будет ли решение NOSQL, такое как MongoDB или CouchDB, лучше подходить для ответа на все вопросы ниже?

Схема базы данных

Ученики
-------
Я БЫ
название

Курсы
-----
Я БЫ
название
класс

Учащиеся
----------
Я БЫ
Студенческий билет
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

Вопросы

1) Получить всех учеников в 9-м классе математического курса

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'

Решение

Этот прост. ActiveRecord хорошо справляется с этим

c = Course.where(:grade=>9).where(:name=>'Math').first
c.students

2) Получить все курсы, принятые Джоном

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'

Решение

Опять все просто.

s = Student.where(:name=>'John').first
s.courses

3) Извлечь все курсы 9-го класса вместе с количеством учащихся, проходящих курс (но не извлекать учащихся)

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

Решение

Counter Cache будет хорошо работать здесь.

класс AddCounters 0
    add_column: курсы,: Students_count,: целое число,: по умолчанию =>0
    Student.reset_column_information
    Student.all.each do | s |
      Student.update_counters s.id,: courses_count=> s.courses.length
    конец
    Course.reset_column_information
    Course.all.each do |c|
      Course.update_counters c.id,: Students_count => c.students.length
    конец
  конец

  опускаться вниз
    remove_column: студенты,:courses_count
    remove_column: курсы,:student_count
  конец
конец

ActiveRecord

Course.where (: grade => 9).each do | c |
  ставит "#{c.name} - #{c.students.size}"
конец

4) Извлеките всех учащихся, проходящих как минимум три курса 11-го класса, более одного курса 10-го класса и никаких курсов 9-го класса

Нет решения

Не уверен в лучшем решении. Это было бы ОЧЕНЬ грязно, если бы в SQL не хранился счетчик кэша для количества курсов для каждого класса на каждого учащегося. Я мог бы добавить хук, чтобы обновить эту информацию сам. Я не хочу тянуть всех студентов и курсы и считать их в постобработке.

Медленное решение

Следующее решение производит много запросов. Предварительная загрузка курсов может быть невозможна. (Например, студенты приходят из ассоциации на курс)

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) Получить всех студентов, которые берут более одного запроса по математике)

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

Или же

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

Нет решения

Не уверен в лучшем решении. Сложность в том, что решение ActiveRecord (или NoSQL) не может извлечь всех студентов и просмотреть их курсы впоследствии, потому что это будет слишком медленно.

Медленное решение

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) Извлеките всех студентов, которые проходят курс математики и естественных наук

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'

Нет решения

Это включает в себя соединение с одной и той же таблицей (или в Rails, ассоциация) дважды. Есть ли способ сделать это гладко с ARR-оберткой ActiveRecord? Вы можете создать отдельную ассоциацию для уроков естествознания и математики, что позволит вам выполнять отдельные операции над каждым, но в случае № 7 ниже это не сработает.

Медленное решение

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) Клиент заявил, что каждый раз, когда в системе отображается ученик, рядом с учеником отображается число, показывающее курс, который он посещает. Например, если Сьюзи проходит курс естествознания для 9-го класса и математика для 10-го класса, отобразите "10" рядом с "Сьюзи".

Решение

Было бы неприемлемо запрашивать базу данных для каждой записи студента. Страница, которая отображает 100 студентов, потребует 100 запросов. На этом этапе я хочу денормализовать базу данных, поместив флаг в таблицу ученика с "курсом высшего уровня". Это мой лучший курс действий? Было бы лучше с самого начала использовать другое хранилище данных, кроме реляционной базы данных?

Представьте, что клиент запросил любые произвольные данные, которые будут отображаться в виде значка: самый высокий уровень оценки, количество пройденных курсов по математике, золотой значок, если все вместе изучают математику, естествознание и историю, и т. Д. Должен ли каждый из этих случаев вызывать денормализацию базы данных? Следует ли хранить денормализованные данные в той же реляционной базе данных, что и нормализованные данные?

Answer 1

Во-первых, я думаю, что ваша схема базы данных в порядке. Я бы НЕ нормализовал на основе этих вариантов использования, так как они очень распространены.

Во-вторых, вы должны научиться различать постоянство, бизнес-логику и отчеты. ActiveRecord хорош для базового сохранения и инкапсуляции бизнес-логики. Он обрабатывает элементы CRUD и позволяет вам вложить в модель большую часть логики вашего приложения. Тем не менее, большая часть логики, о которой вы говорите, звучит как отчеты, особенно № 6. Вы должны будете принять, что для какой-то логики запросов, подобной этой, сырой SQL будет вашим лучшим выбором. Я думаю, что счетчики кеша, которые вы внедрили, могут помочь вам оставаться в активной записи и моделях, если вам там удобнее, но, скорее всего, вам придется перейти на простой sql, как вы делали для некоторых из этих решений. Отчеты вообще требуют прямой sql.

Нормализованная база данных имеет решающее значение для хорошего дизайна приложения. Это действительно важно для того, чтобы сделать ваш код чистым для транзакций OLTP и бизнес-логики. Не денормализуйте только потому, что вам нужно сделать несколько соединений в SQL. Это то, что sql хорош в. Все, что вы собираетесь сделать путем денормализации, - это ускорить и упростить некоторую логику отчетности за счет дорогостоящего замедления и усложнения вашей настойчивости и логики OLTP.

Так что я бы начал держать вашу нормализованную базу данных. Если вам нужно присоединиться к связанной таблице, вы часто можете использовать метод include activerecord, чтобы сделать это, не прибегая к обычному sql. Чтобы делать такие вещи, как подсчет, основанный на соединениях, вы должны будете использовать обычный sql.

В конце концов, если ваша база данных станет очень большой с большим количеством данных, ваши отчеты будут медленными из-за всех объединений, которые вам придется делать. Это отлично. В этот момент, и не раньше, начните рассматривать создание отдельной денормализованной базы данных отчетов, которую можно обновлять ежечасно, еженедельно, еженедельно и т. Д. Из нормализованной базы данных. Затем переместите логику создания отчетов, чтобы запрашивать базу данных отчетов без необходимости объединений. Однако нет необходимости начинать с этого пути. Вы просто несете дополнительную сложность и затраты, не будучи уверенными в выигрыше. Возможно, ваши отчеты sql с объединениями будут работать бесконечно без денормализации с использованием индексов. Не преждевременно оптимизировать.

Я не думаю, что nosql - это тоже обязательно ответ. Из того, что я мало знаю, NoSQL хорошо работает для конкретных случаев использования. Варианты использования и схема вашего приложения прекрасно подходят для реляционных баз данных.

В целом, я думаю, что комбинация raw sql (не arel/activerecord) и счетчиков, которые вы внедрили, в порядке.

Answer 2

Я сталкиваюсь с той же проблемой в данный момент. Из моего исследования есть несколько способов обойти это.

Прежде всего, я считаю, что любое приложение столкнется с этими проблемами. Основная идея заключается в том, что мы моделируем наши данные нормализованным способом, который по своей сути становится медленным и громоздким, когда имеется много данных и объем данных в нескольких таблицах.

Лучший подход, который я смог придумать, заключается в следующем:

1. Смоделируйте проблему как можно ближе к реальному миру, над которым вы работаете
2. Нормализовать по мере необходимости

Эти два должны дать большую гибкость для приложения и обеспечить много удобных методов, а также решить большинство вопросов, на которые я пытаюсь ответить

Как только мне нужно сделать несколько соединений, чтобы получить то, что мне нужно, и я чувствую, что мне нужно денормализовать таблицы, чтобы легко добраться до того, что мне нужно, я рассматриваю следующее:

Представления SQL: это предопределенные операторы sql, например, объединения, с которыми я могу связать модели. Обычно это намного быстрее, чем запросы через ActiveRecord. http://hashrocket.com/blog/posts/sql-views-and-activerecord

таблица агрегирования: создайте одну или несколько таблиц агрегирования и обновите асинхронно, используя delayed_job, например, resque. Например, эти агрегаты могут обновляться один раз в день, а модель может запрашивать их напрямую. Обратите внимание, что это своего рода денормализованная таблица.

Couchbase (NOSQL) Я не использовал этот, но выглядит очень интересно. http://couchbaseonrails.com/understand