Как опитмально поступить в передаче состояния у наследников (foreignkey)

@BeginnerCoderCS · Регистрация: 10.01.2020

Студворк — интернет-сервис помощи студентам

Всем доброе утро!!!

Представим вот такую задачу.

Есть некая онлайн школа допустим программирования.
В ней есть несколько курсов по разным ЯП (таблица Course).
Есть студенты (таблица Student), которые могут состоять ТОЛЬКО в одном курсе. То есть ТОЛЬКО один язык.
В кажом курсе может быть до 1000 студентов.

Есть структура базы

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `Course` (
    `Id` INT(11) NOT NULL AUTO_INCREMENT,
    `Name` VARCHAR(50) NOT NULL,
    `Status` INT(11) NOT NULL
);
 
 
 
CREATE TABLE `Student` (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Name` VARCHAR(50) NOT NULL,
    `Status` INT NOT NULL,
    `CourseId` INT NOT NULL,
    CONSTRAINT `FK_course` FOREIGN KEY (`CourseId`) REFERENCES `Course` (`Id`)
);

У каждого студента есть свой статус/состояние учёбы в этом курсе.
Статусов ограниченное количество, поэтому они вынесены в код C# в enum, а в базу передаётся только число этого enum для оптимизации работы БД.
Допустим

Принят = 0,
Сдал = 1,
Должник = 2,
Не Сдал = 3,
Отсутствовал = 4,

Так вот задача.

В таблицу Course нужно передавать статус от всех студентов.
Вот что я имею в виду:
Есть курс "Программирование C#".
Есть 2 студента в этом курсе.
Вася со статусом "Не Сдал" = 3
Петя со статусом "Сдал" = 1

Нужно установить статус курсу, по самому безотвественному студенту, так как курс не может быть закрыт/закончен (со статусом "Сдал" = 1) до тех пор пока хоть один студент находится в другом состоянии.

Логика должна быть примерно такая:
Если в курсе есть хоть один студент (Student.Status) который в статусе Отсутствовал = 4, то и в Course.Status должно быть Отсутствовал = 4.
Если есть хоть один Student.Status Не Сдал = 3, то и в Course.Status должно быть Не Сдал = 3.
Если есть хоть один Student.Status Должник = 2, то и в Course.Status должно быть Должник = 2.
Если есть хоть один Student.Status Сдал = 1, то и в Course.Status должно быть Сдал = 1.
Если есть хоть один Student.Status Принят = 0, то и в Course.Status должно быть Принят = 0.

Данные в таблице по студентам обновляются каждый час.
То есть каждый час, после обновления таблицы Student нужно проверять статусы наследников, и устанавливать новые статусы в таблицу Course.

Данных в таблице (Student) будет более 10млн. И данных в таблице (Course) будет больше 1млн.

Как это сделать с минимальной выгрузкой всех данных в программу?

Или ещё лучше как сформировать запрос SQL чтобы это всё происходило на стороне СУБД, без получения данных в программу.
Или может SQL запрос который получает полуобработанные данные вида:
CourseId - Status(уже проверенный)

Или может уже существует подход к такой задаче.

p.s. Используется MySQL база и библиотека linq2db.

@Элд Хасп · 03.06.2021, 10:23

Сообщение от BeginnerCoderCS

Элд Хасп,... что может подскажете?

Сообщение от BeginnerCoderCS

Как это сделать с минимальной выгрузкой всех данных в программу?

Насколько я себе представляю - только SQL.
Но в этом я ничего вам подсказать не могу.
Изучал его лет тридцать назад - уже всё забыл.

Добавлено через 14 минут
На Шарпе это будет без больших проблем.
Что-то в таком духе:

C#
1
2
3
4
5
6
7
8
9
using(var db = new ....)
{
    db.Courses.ToList()
        .ForEach(course => course.Status = db.Students.Where(st => course.Id == st.CourseId)
            .Select(st => st.Status)
            .Min()
        );
   db.SaveChanges();
}

Но такая реализация приведёт (насколько в этом я разбираюсь) к полной выгрузке всех записей БД.

В разделе, думаю, смогут подсказать как правильно это сделать на стороне сервера.

@carrotik · 03.06.2021, 10:28

BeginnerCoderCS,
.. ваша логика не совсем понятна ... если статус курса устанавливается по "самому безответственному", т.е. прогульщику со статусом 4, то тогда можно что-то типа

SQL
1
2
SELECT @courseId = Id FROM Course WHERE Name = 'Программирование C#'
UPDATE Course SET STATUS = (SELECT MAX(STATUS) FROM Student WHERE CourseId = @courseId) WHERE Id = @courseId

@BeginnerCoderCS · 03.06.2021, 10:39 **[ТС]**

Сообщение от Элд Хасп

Но такая реализация приведёт (насколько в этом я разбираюсь) к полной выгрузке всех записей БД.

Да, будет полная выгрузка, а данных очень много.

Но в любом случае спасибо за отклик.

Добавлено через 8 минут

Сообщение от carrotik

.. ваша логика не совсем понятна ... если статус курса устанавливается по "самому безответственному", т.е. прогульщику со статусом 4, то тогда можно что-то типа

"самому безответственному" почти так, но не совсем.
В примере так получилось случайно.
Нельзя привязываться к возростанию порядкового номера.

Допустим важность статуса должна быть в таком порядке
Принят = 0
Отсутствовал = 4
Должник = 2
Не Сдал = 3
Сдал = 1

Если есть хоть один студент со статусом Принят = 0, то курсу установим Принят = 0,
Если есть хоть один студент со статусом Отсутствовал = 4 то курсу установим Отсутствовал = 4
и т.д.

Так как в будущем может измениться логика наследования статуса, и порядок важности статусов будет другим.

То есть без привязки возрастания числа.

@Элд Хасп · 03.06.2021, 10:48

BeginnerCoderCS, дополнение к ответу от carrotik.
Наверное, имеет смысл в таблице Студентов задать дополнительную индексацию по сочетанию ключей CourseId и Status.
В таком случае необходимо для минимального/максимального Статуса выбрать только одну первую/последнюю запись по этому индексу.

Добавлено через 8 минут

Сообщение от BeginnerCoderCS

Так как в будущем может измениться логика наследования статуса, и порядок важности статусов будет другим.
То есть без привязки возрастания числа.

Это уже в принципе неверно.
Как запрос должен определить приоритеты статусов?
Он же не может "залезть в голову" и догадаться какой из них выше - какой ниже.
Приоритет должен быть либо неявно задан значением самого Статуса.
Либо необходима ещё одна дополнительная таблица, по которой определяется приоритетность Статусов.

@BeginnerCoderCS · 03.06.2021, 10:56 **[ТС]**

Сообщение от Элд Хасп

Он же не может "залезть в голову" и догадаться какой из них выше - какой ниже.

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

Сообщение от Элд Хасп

Либо необходима ещё одна дополнительная таблица, по которой определяется приоритетность Статусов.

Но можно и так задать приоритет, если это облегчит базу.

@carrotik · 03.06.2021, 10:56

Сообщение от BeginnerCoderCS

Если есть хоть один студент со статусом Принят = 0, то курсу установим Принят = 0,
Если есть хоть один студент со статусом Отсутствовал = 4 то курсу установим Отсутствовал = 4

,. а если полмиллиона принято, а среди них сто тыщ прогульщиков? ... я до сих пор не понял приоритета ...
..но в любом случае можно использовать where status <> 3 or .....

@BeginnerCoderCS · 03.06.2021, 11:54 **[ТС]**

Сообщение от carrotik

,. а если полмиллиона принято, а среди них сто тыщ прогульщиков? ... я до сих пор не понял приоритета ...
..но в любом случае можно использовать where status <> 3 or .....

Пример с курсами - просто пример. На самом деле там всё гораздо сложнее, и я вряд ли бы смог это всё объяснить, чтобы было понято. Поэтому привёл пример с курсами.

Если учесть что приоритет вот такой.
Чем выше тем важнее.
Принят = 0
Отсутствовал = 4
Должник = 2
Не Сдал = 3
Сдал = 1

Пример 1:
В курсе "C#" 100 студентов.
Из них
50 студентов Сдал = 1
49 студентов Должник = 2
1 студент Отсутствовал = 4

Значит Курсу "C#" присвоить статус Отсутствовал = 4,
так как статус Отсутствовал = 4 из всех имеющихся в курсе выше всего в приоритете.

Пример 2:
В курсе "Java" 1000 студентов.
Из них
500 студентов Сдал = 1
450 студентов Принят = 0
49 студентов Не Сдал
1 студент Отсутствовал = 4

Значит Курсу "Java" присвоить статус Принят = 0,
так как статус Принят = 0 из всех имеющихся в курсе выше всего в приоритете.

Пример 3:
В курсе "SQL" 100 студентов.
Из них
99 студентов Сдал = 1
1 студент Не Сдал = 3

Значит Курсу "SQL" присвоить статус Не Сдал = 3,
так как статус Не Сдал = 3 из всех имеющихся в курсе выше всего в приоритете.

Желательно не смотрите на логику самого статуса (принят или сдал), а просто на последовательность важности. Это сделано для примера.

Добавлено через 29 минут
carrotik, накидал простенький пример, как это сделать на C#. Надеюсь будет понятнее.

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
public class Course
{
    public int Id;
    public string Name;
    public int Status;
}
public class Student
{
    public int Id;
    public string Name;
    public int Status;
    public int CourseId;
}
 
public void UpdateCourseStatus()
{
    var course = new Course
    {
        Id = 777,
        Name = "C#",
        Status = -1, // нельзя null
    };
 
    List<Student> students = new List<Student>
    {
        new Student{ Id = 1, Name = "Иван", CourseId = 777, Status = 1}, // Сдал
        new Student{ Id = 2, Name = "Пётр", CourseId = 777, Status = 2 }, // Должник
        new Student{ Id = 3, Name = "Фёдор", CourseId = 777, Status = 3 }, // Не Сдал
    };
 
 
    // Список последовательности приоритетов.
    // Принят = 0
    // Отсутствовал = 4
    // Должник = 2
    // Не Сдал = 3
    // Сдал = 1
    // В нашем случае Статус Должник = 2 (который у Петра) должен стать статуом курса C#
    List<int> priority = new List<int> { 0, 4, 2, 3, 1 };
 
    for (int i = 0; i < priority.Count; i++)
    {
        var status = students
            .Where(x => x.Status == priority[i])
            .Select(x => x.Status)
            .FirstOrDefault();
 
        if (status != 0)
        {
            course.Status = status;
            break;
            // Как только нашли самый приоритетный, устанавливаем, и дальше не ищем.
        }
    }
}

@Igr_ok · 03.06.2021, 12:20

BeginnerCoderCS, добавьте в таблицу статусов приоритет и получайте не статус, а макс или мин приоритет.

@BeginnerCoderCS · 03.06.2021, 13:23 **[ТС]**

Сообщение от Igr_ok

добавьте в таблицу статусов приоритет и получайте не статус, а макс или мин приоритет.

Можно чуть подробнее?

@Элд Хасп · 03.06.2021, 13:31

Сообщение от BeginnerCoderCS

Но можно и так задать приоритет, если это облегчит базу.

Обязательно.
Поскольку по другому невозможно будет выполнить на стороне сервера.
У сервера должна быть вся необходимая информация.

Добавлено через 3 минуты

Сообщение от BeginnerCoderCS

Можно чуть подробнее?

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public class Status
{
    public int Id;
    public string Title;
    public int Priority;
}
 
public class Course
{
    public int Id;
    public string Name;
    public int StatusId;
}
public class Student
{
    public int Id;
    public string Name;
    public int StatusId;
    public int CourseId;
}

@Igr_ok · 03.06.2021, 14:39

Сообщение от BeginnerCoderCS

Можно чуть подробнее?

Создаете таблицу Statuses. Поля StatusID, StatusName, Priority.
Тогда запрос будет выглядеть так:

T-SQL
1
2
3
4
select CourseId, min(Statuses.Priority) 
from Student 
inner join Statuses on Student.Status=Statuses.StatusID
Group by CourseId

@MsGuns · 03.06.2021, 15:36

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

Все дела

@BeginnerCoderCS · 03.06.2021, 16:50 **[ТС]**

Сообщение от MsGuns

При любом изменении любого студента автоматом в курс пишется новый статус.

На сколько это будет затратная операция?
Частота изменения студентов планируется один раз в час, если были изменения.
То есть грубо в таблице студентов 10млн строк.
И каждый час будет меняться значения ~ от 10 000 до 500 000 студентов

Это не положит субд?

@MsGuns · 03.06.2021, 17:35

Сообщение от BeginnerCoderCS

Это не положит субд?

MySQL ? Не положит

Мне трудно представить не веб систему, в которой одновременно работают десятки тысяч реальных юзеров.

@Элд Хасп · 03.06.2021, 17:38

Сообщение от BeginnerCoderCS

И каждый час будет меняться значения ~ от 10 000 до 500 000 студентов

Если update БД предусматривается только редкое пакетное, то оптимальнее будет внесение связанных изменений после update.
Если изменения частые и единичные, то вариант MsGuns предпочтительнее.

Добавлено через 1 минуту
BeginnerCoderCS, просто такое обновление (очень редкое и большими пакетами) довольно не типично и несклоько ставит в тупик, как уже заметил MsGuns.

@MsGuns · 03.06.2021, 17:43

Не положит по двум причинам.
Во-первых триггеры сами по себе работают ну ооочень быстро. 1млн срабатываний займет несколько сек.
Во вторых, хранимка выполняет два действия: 1. вычисление min по единственной таблице, к тому же по "короткому" полю - это работает мгновенно даже на миллионе записей. 2. Update единственной записи (по Id) - это доли мс + проходит далеко не всегда - если новый статус ниже вычисленного. Итого, как правило, все ограничится лишь выборкой без апдейта.
Т.е. вся транзакция будет занимать макс. 1-2 милисекунды.

@Igr_ok · 03.06.2021, 17:53

Сообщение от BeginnerCoderCS

Это не положит субд?

Триггер будет срабатывать после обновления каждой записи. Т.е.

Сообщение от BeginnerCoderCS

от 10 000 до 500 000

раз в час. А извлечение данных запросом будет только тогда, когда эти данные кому-то понадобятся. Что вам подсказывает здравый смысл?)
Пара советов.
1.Используйте триггеры только тогда, когда без них не обойтись.
2.Храните в таблице вычисляемое поле только тогда, когда логика его вычисления затратна по времени.

@MsGuns · 03.06.2021, 18:03

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

Добавлено через 4 минуты

Сообщение от Igr_ok

раз в час. А извлечение данных запросом будет только тогда, когда эти данные кому-то понадобятся

А вот тут не понял. Триггер будет срабатывать раз в час ?

Сообщение от Igr_ok

1.Используйте триггеры только тогда, когда без них не обойтись.

Да что Вы говорите.. Серьезно ? Тогда приведите пример, "когда без них не обойтись".

Сообщение от Igr_ok

2.Храните в таблице вычисляемое поле только тогда, когда логика его вычисления затратна по времени.

О каком вычисляемом поле идет речь ? Min по всем студентам данного курса ?

Добавлено через 1 минуту
Поправка: вторичный индекс вешается на таблицу студентов, конечно же,- ведь именно из нее определяется минимальный статус

@Igr_ok · 03.06.2021, 18:26

MsGuns, в 6 ТС объяснил, почему одного поля статус недостаточно. А на троллинг я не ведусь

Новые блоги и статьи Все статьи Все блоги /
http://iceja.net/ математические сервисы iceja 20.01.2026 Обновила свой сайт http:/ / iceja. net/ , приделала Fast Fourier Transform экстраполяцию сигналов. Однако предсказывает далеко не каждый сигнал (см ограничения http:/ / iceja. net/ fourier/ docs ). Также. . .	http://iceja.net/ сервер решения полиномов iceja 18.01.2026 Выкатила http:/ / iceja. net/ сервер решения полиномов (находит действительные корни полиномов методом Штурма). На сайте документация по API, но скажу прямо VPS слабенький и 200 000 полиномов. . .	Расчёт переходных процессов в цепи постоянного тока igorrr37 16.01.2026 / * Дана цепь постоянного тока с R, L, C, k(ключ), U, E, J. Программа составляет систему уравнений по 1 и 2 законам Кирхгофа, решает её и находит переходные токи и напряжения на элементах схемы. . . .	Восстановить юзерскрипты Greasemonkey из бэкапа браузера damix 15.01.2026 Если восстановить из бэкапа профиль Firefox после переустановки винды, то список юзерскриптов в Greasemonkey будет пустым. Но восстановить их можно так. Для этого понадобится консольная утилита. . .
Сукцессия микоризы: основная теория в виде двух уравнений. anaschu 11.01.2026 https:/ / rutube. ru/ video/ 7a537f578d808e67a3c6fd818a44a5c4/	WordPad для Windows 11 Jel 10.01.2026 WordPad для Windows 11 — это приложение, которое восстанавливает классический текстовый редактор WordPad в операционной системе Windows 11. После того как Microsoft исключила WordPad из. . .	Classic Notepad for Windows 11 Jel 10.01.2026 Old Classic Notepad for Windows 11 Приложение для Windows 11, позволяющее пользователям вернуть классическую версию текстового редактора «Блокнот» из Windows 10. Программа предоставляет более. . .	Почему дизайн решает? Neotwalker 09.01.2026 В современном мире, где конкуренция за внимание потребителя достигла пика, дизайн становится мощным инструментом для успеха бренда. Это не просто красивый внешний вид продукта или сайта — это. . .

@Igr_ok 785 / 616 / 273 Регистрация: 04.08.2015 Сообщений: 1,713
	03.06.2021, 12:20
	BeginnerCoderCS, добавьте в таблицу статусов приоритет и получайте не статус, а макс или мин приоритет. 1

@MsGuns 1497 / 1238 / 245 Регистрация: 04.04.2011 Сообщений: 4,363
	03.06.2021, 15:36
	На таблицу студентов вешается триггер AfterUpdate, в котором вызывается хранимка, в котрой вычисляется минимальный статус по курсу, где студент. Этот минимальный статус записывается в поле статуса курса так, как было указано выше. При любом изменении любого студента автоматом в курс пишется новый статус. Никаких "почасовых" проверок-изменений не нужно, никакой таблицы статусов тоже. Все делается на сервере, ничего никуда не тянется. Все дела 2

@MsGuns 1497 / 1238 / 245 Регистрация: 04.04.2011 Сообщений: 4,363
	03.06.2021, 17:43
	Не положит по двум причинам. Во-первых триггеры сами по себе работают ну ооочень быстро. 1млн срабатываний займет несколько сек. Во вторых, хранимка выполняет два действия: 1. вычисление min по единственной таблице, к тому же по "короткому" полю - это работает мгновенно даже на миллионе записей. 2. Update единственной записи (по Id) - это доли мс + проходит далеко не всегда - если новый статус ниже вычисленного. Итого, как правило, все ограничится лишь выборкой без апдейта. Т.е. вся транзакция будет занимать макс. 1-2 милисекунды. 2

@Igr_ok 785 / 616 / 273 Регистрация: 04.08.2015 Сообщений: 1,713
	03.06.2021, 18:26
	MsGuns, в 6 ТС объяснил, почему одного поля статус недостаточно. А на троллинг я не ведусь 1