Блог

При работе с SQL Server (через Entity Framework Core 3) и иметь требование добавлять участников в группу, но с ограничением количества участников, разрешенных в каждой группе. Я проиллюстрирую:

Структура таблицы:

 [ID] [bigint] IDENTITY(1,1) NOT NULL, 
[MemberId] [bigint] NOT NULL, 
[GroupNumber] [bigint] NOT NULL, 
[PositionInGroup] [int] NOT NULL
  

Каждый раз, когда я добавляю новую строку в эту таблицу, мне нужно вычислить «GroupNumber» и «PositionInGroup» для нового члена. В каждом «GroupNumber» может быть только 5 элементов.

Итак, при добавлении новой записи мне нужно…

1. Получить наибольшее значение для номера «GroupNumber»
2. Подсчитайте количество людей в GroupNumber
3. Увеличьте PositionInGroup в GroupNumber и перенесите на «0», если больше 5 (максимально допустимый в группе)
4. Если позиция в группе равна нулю (т. Е. Мы перевернулись), увеличьте GroupNumber на 1
5. Создайте новую запись со значением GroupNumbernumber и PositionInGroup

Поскольку существует ряд вычислений, которые должны выполняться на основе строк в таблице, каков наилучший способ управлять этим в SQL Server (или EF Core Framework), чтобы предотвратить присвоение членам неправильных / повторяющихся номеров групп / позиций из-за одновременных запросов на добавление группы?

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

Если это имеет значение, я ожидаю не более 500 таких вызовов в течение 12-часового периода, поэтому я не очень беспокоюсь о конфликте блокировок — я забочусь в первую очередь о целостности данных.

Вот как я ожидаю, что данные будут выглядеть после 15 вставок. Обратите внимание, что счетчик «PositionInGroup» сбрасывается после достижения максимального значения PositionInGroup, равного 5.

Член может находиться только в одной группе в одной позиции. Как только член находится в группе на определенной позиции, он никогда не может измениться. Элемент будет добавлен в эту таблицу только один раз.

Заранее спасибо.

Интересное упражнение. Я бы выбрал вычисляемые столбцы для обеспечения полной целостности.

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

настройка / ввод

 DECLARE @test TABLE (
    memberId int identity(1,1),
    groupNumber as (memberId - 1) / 5   1
);

insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values
insert into @test default values

select *, 
  row_number() over (partition by groupNumber order by memberId) as positionInGroup
from @test
order by memberId
  

вывод

 
 ---------- ------------- ----------------- 
| memberId | groupNumber | positionInGroup |
 ---------- ------------- ----------------- 
|        1 |           1 |               1 |
|        2 |           1 |               2 |
|        3 |           1 |               3 |
|        4 |           1 |               4 |
|        5 |           1 |               5 |
|        6 |           2 |               1 |
|        7 |           2 |               2 |
|        8 |           2 |               3 |
|        9 |           2 |               4 |
|       10 |           2 |               5 |
|       11 |           3 |               1 |
|       12 |           3 |               2 |
|       13 |           3 |               3 |
|       14 |           3 |               4 |
|       15 |           3 |               5 |
 ---------- ------------- -----------------