Предложение — коротко и по делу.
Расширенная нормализованная схема (ключевые таблицы и смысл полей):
- students(student_id PK, name, ...)
- courses(course_id PK, title, ...)
- terms(term_id PK, name, start_date, end_date) — семестры/периоды
- course_offerings(offering_id PK, course_id FK, term_id FK, ...) — конкретный курс в семестре
- enrollments(enrollment_id PK, student_id FK, offering_id FK, enrolled_at, ...) — факт записи студента на offering
- grade_history(grade_id PK, enrollment_id FK, grade NUMERIC, valid_from TIMESTAMP, valid_to TIMESTAMP NULL, created_by, created_at, reason) — временные записи оценок (period-validity)
Концепция:
- grade_history хранит историю оценок как интервалы допустимости: запись активна на отрезке $[valid\_from,valid\_to)$. Текущая оценка — запись с $valid\_to$ = NULL.
- Для быстрого чтения текущих значений можно иметь материализованную/денормализованную таблицу current_grades(enrollment_id PK, grade, updated_at, ...) или поддерживать индекс и частичный запрос на grade_history с условием $valid\_toISNULL$.
Обеспечение консистентности:
- Все изменения оценок в транзакции: новая запись вставляется и старая закрывается (обновляется её valid_to) в одной транзакции.
- Ограничения/индексы:
- уникальный частичный индекс, гарантирующий не более одной активной записи на enrollment:
- (в PostgreSQL) `CREATE UNIQUE INDEX ON grade_history(enrollment_id) WHERE valid_to IS NULL;`
- запрет перекрывающихся интервалов (Postgres): `EXCLUDE USING gist (enrollment_id WITH =, tstzrange(valid_from, coalesce(valid_to, 'infinity')) WITH &&)`
- FK-ограничения между grade_history → enrollments → students/courses.
- Триггер/проверки при вставке: автоматически устанавливать previous.valid_to = new.valid_from при обновлении оценки, логировать автора и причину.
Как обеспечить быстрый запрос последних оценок при больших объёмах:
- Хранить current_grades (обновляемая таблица/материализованный вид) — чтение средней/агрегатов идёт из неё без сканирования истории.
- Индексы:
- current_grades: индекс по (student_id), по (student_id, term_id) или покрывающий индекс.
- если читаем сразу из grade_history: частичный индекс `(enrollment_id, valid_from DESC) WHERE valid_to IS NULL` или индекс по `(student_id, valid_to)` через join.
- Партиционирование grade_history по term_id или по году/семестру для архивации и быстрых запросов на недавние данные.
- Использовать реплики/Read-only узлы для аналитики, и инкрементальные обновления materialized view при большом потоке записей.
- Для массовых откатов по дате — операциям rollback лучше добавлять новые записи (реверт) или править valid_to в одной транзакции, а не удалять историю.
Пример оптимизированного SQL (предполагается наличие current_grades или что `grade_history` содержит текущую запись с `valid_to IS NULL` и связи enrollment→offering→term). Параметры: student id = $:studen{t}_{i}d$, term id = $:ter{m}_{i}d$.
Вариант A — запрос по текущим оценкам в denormalized current_grades:
SELECT AVG(g.grade) AS avg_grade
FROM current_grades g
JOIN enrollments e ON g.enrollment_id = e.enrollment_id
JOIN course_offerings o ON e.offering_id = o.offering_id
WHERE e.student_id = $:studen{t}_{i}d$ AND o.term_id = $:ter{m}_{i}d$;
Вариант B — если читаем из grade_history (Postgres) — берем только активные записи:
SELECT AVG(gh.grade) AS avg_grade
FROM grade_history gh
JOIN enrollments e ON gh.enrollment_id = e.enrollment_id
JOIN course_offerings o ON e.offering_id = o.offering_id
WHERE gh.valid_to IS NULL
AND e.student_id = $:studen{t}_{i}d$ AND o.term_id = $:ter{m}_{i}d$;
Оптимизации для этого запроса:
- Индекс на enrollments(student_id, enrollment_id) или покрывающий индекс.
- Индекс на course_offerings(term_id, offering_id).
- Частичный индекс на grade_history(enrollment_id, grade) WHERE valid_to IS NULL (позволит быстро выбирать только текущие оценки).
- Если запрос делается очень часто — materialized view current_grades_by_term(student_id, term_id, grade) либо агрегатный кэш по (student_id, term_id) для мгновенного ответа.
Как выполнить откат к дате:
- Для отката к моменту T: в транзакции вставлять новую запись с valid_from = T и корректировать/устанавливать valid_to предыдущих записей; либо восстанавливать соответствующие valid_from/valid_to для того состояния. Всю операцию делать транзакционно, логируя действие.
Коротко: храните историю как интервалы в grade_history, держите одну активную запись (valid_to IS NULL), поддерживайте отдельную таблицу/материализованный вид для текущих оценок и индексы/партиции по student_id и term_id — это обеспечит консистентность, быстрые выборки и возможность отката по дате.