Как правильно хранить списки в MS SQL? Как хранить просто данные о том, что есть множество A и оно состоит из элементов a1, a2 и a3 я понимаю, но как правильно хранить информацию о том, что у меня есть список из элементов a1, a2 и a3, в который пользователь может вставить элемент a4 на вторую позицию и список станет a1, a4, a2, a3, я не понимаю.

Вот допустим есть две таблицы:

CREATE TABLE [dbo].[Item]
(
Item_ID INT IDENTITY(1,1) NOT NULL,
Name NVARCHAR(50) NOT NULL,
CONSTRAINT PK_Item PRIMARY KEY CLUSTERED(Item_ID)
);

CREATE TABLE [dbo].[Zone]
(
Zone_ID INT IDENTITY(1,1) NOT NULL,
Name NVARCHAR(50) NOT NULL,
CONSTRAINT PK_Zone PRIMARY KEY CLUSTERED(Zone_ID)
);

И мне нужно хранить информацию о расположении элементов в зонах.

Я могу банально сделать вот так:

CREATE TABLE [dbo].[Item_Zone_Position]
(
Item_ID INT NOT NULL,
Zone_IDINT NOT NULL,
Position INT NOT NULL,
CONSTRAINT PK_Item_Zone_Position PRIMARY KEY CLUSTERED(Item_ID),
CONSTRAINT FK_Item_Zone_Position_Item FOREIGN KEY(Item_ID)
REFERENCES [dbo].[Item](Item_ID),
CONSTRAINT FK_Item_Zone_Position_Zone FOREIGN KEY(Zone_ID)
REFERENCES [dbo].[Zone](Zone_ID)
);

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

Есть такой вариант, его проще обновлять, но для чтения придётся использовать рекурсивное CTE:

CREATE TABLE [dbo].[Item_Zone_Position_V2]
(
Item_ID INT NOT NULL,
Zone_IDINT NOT NULL,
Previous_Item_ID INT NULL,
CONSTRAINT PK_Item_Zone_Position_V2 PRIMARY KEY CLUSTERED(Item_ID),
CONSTRAINT FK_Item_Zone_Position_V2_Item FOREIGN KEY(Item_ID)
REFERENCES [dbo].[Item](Item_ID),
CONSTRAINT FK_Item_Zone_Position_V2_Zone FOREIGN KEY(Zone_ID)
REFERENCES [dbo].[Zone](Zone_ID),
CONSTRAINT FK_Item_Zone_Position_V2_Item2 FOREIGN KEY(Item_ID)
REFERENCES [dbo].[Item](Item_ID)
);

Есть ещё вариант с тем, чтобы хранить в таблице с позициями ещё и версию расположения элементов в конкретной зоне и вместо UPDATE делать INSERT, но тогда таблицу придётся как-то чистить от предыдущих состояний и как-то избегать ситуаций, когда две транзакции пытаются записать туда данные одновременно (предыдущие два варианта меня подкупают тем, что они должны работать без явно указанных блокировок на стандартном уровне изоляции транзакций).

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

Так как лучше будет, чтобы быстро, безопасно и без дедлоков?

>>709950

Ну это примерно соответствует моему первому варианту.

А что будет в том случае, когда кто-то будет читать данные из таблицы, по которой идёт этот UPDATE? Часть таблицы заблокирует UPDATE, часть SELECT и скажи привет дедлоку. Если бы я контролировал то, как эти запросы расставляют блокировки, то я бы просто сделал бы так, чтобы они блокировали требуемые им ресурсы в одном и том же порядке, что исключило бы вероятность дедлоков, но у меня нет такой возможности (по крайней мере я не знаю способов так сделать).

Ещё я не понял как обстоят дела с индексами и ключами: судя по коду твоего примера там нет ни ключей, ни индексов на столбце order, т.е. если я буду играться с блокировками, то этот
update table
set order = order - 1
where order >= ? and order <= ?;

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

Я имею ввиду ситуацию:

Транзакция 1:

1. Выкидывает из плейлиста песню с номером 5.
2. Песни с номерами больше 5 уменьшают свой номер на 1.

Транзакция 2:

1. Определяет последний номер песни в плейлисте.
2. Вставляет новую песню с номером на 1 больший, чем максимальный.

Если эти операции физически выполнятся вот так, то фигня получится:

1. Транзакция 2, шаг 1.
2. Транзакция 1, шаг 1.
3. Транзакция 1, шаг 2.
4. Транзакция 2, шаг 2.

В итоге получится, что после первой транзакции список 1, 2,3 ... 100 превратится в 1,2,3,..99, а вторая транзакция превратит его в 1,2,...99,101. Ну и само собой это не единственный вариант такой коллизии, могут быть ещё в варианты с присвоением одного и того же номера двум элементам одновременно.

Ананасы, дайте подсказку, пожалуйста?

Объясню на примере. Пусть есть такие таблицы:

1) Таблица "Сотрудники"
---------------------------------------------------------------
| id сотр |Табельный номер| ФИО |
---------------------------------------------------------------
| 0 | 1 |Иванов И. К. |
| 1 | 2 |Иванов И. А. |
| 2 | 3 |Иванов И. Б. |
---------------------------------------------------------------
2) Таблица "Услуги"
-------------------------------------------------
| id усл |Наименование услуги|
--------------------------------------------------
| 0 |Массаж простаты |
| 1 |Отбеливание ануса |
--------------------------------------------------
3) Таблица "Услуги сотрудников"
-----------------------------------------------------------
| id усл сотр | id сотр | id усл |
-----------------------------------------------------------
| 0 | 0 | 0 |
| 1 | 0 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 0 |
-----------------------------------------------------------

Как можно получить такое:
----------------------------------------------------------------------------------
|Наименование услуги| ФИО сотрудников, оказывающих услугу|
----------------------------------------------------------------------------------
| Массаж простаты | Иванов И. К. |
| | Иванов И. Б. |
----------------------------------------------------------------------------------
|Отбеливание ануса | Иванов И. К. |
| | Иванов И. А. |
----------------------------------------------------------------------------------
?

Я даже точно не знаю, как сформулировать запрос для гугла. Студент на Access и C#, если что.