У меня есть приложение для создания списка покупок, в котором пользователи могут перетаскивать произвольное количество элементов в список. Элементы организованы в двумерном порядке, каждый из которых имеет целочисленную позицию, то есть индексы. Как это часто бывает с этим доменом, никакие два элемента не могут иметь одинаковую позицию.
В настоящее время это представлено в следующей таблице:
id list_id name position
------------------------------------
1 1 apple 0
2 1 banana 1
3 1 orange 2
4 2 milk 3
list_id
- это внешний ключ для другой таблицы (lists
).
Эта таблица имеет следующее уникальное ограничение:
ALTER TABLE public.items
ADD CONSTRAINT unique_position
UNIQUE (list_id, position)
DEFERRABLE INITIALLY IMMEDIATE;
Это помогает поддерживать целостность данных и предотвращает сценарии, когда из-за ошибки в приложении (или непослушного пользователя) два элемента вставляются в одно и то же место. (Причина, по которой это DEFERRABLE
, заключается в том, что ограничение необходимо отложить до конца транзакции, содержащей несколько операторов UPDATE position
.)
Теперь я хочу добавить функцию, с помощью которой пользователь может также создавать подсписки внутри основного списка и вставлять элементы в эти подсписки аналогичным образом последовательно.
Я планирую добавить столбец sublist_id
в исходную таблицу items
:
id list_id name position sublist_id
-----------------------------------------------------
1 1 apple 0 NULL
2 1 banana 1 NULL
3 1 orange 2 NULL
4 2 milk 0 NULL
5 2 chicken 1 NULL
6 2 eggs 2 NULL
7 2 coke NULL 1
8 2 water NULL 1
И есть ссылка на вторую таблицу с именем sublists
:
id list_id name position_in_list
------------------------------------------
1 2 drinks 3
Затем соедините две таблицы, используя третью таблицу с именем sublist_items
:
id sublist_id item_id position
----------------------------------------
1 1 7 0
1 1 8 1
Проблема заключается в том, что в таблице items
некоторые позиции элементов будут указаны как NULL, поскольку эти элементы теперь находятся внутри подсписка, и достаточно отслеживать положение подсписка внутри основного списка и позиции элементов. внутри подсписка.
ВОПРОС 1: Как можно изменить исходное ограничение так, чтобы оно применялось к двум таблицам? Другими словами, я по-прежнему хочу, чтобы каждый элемент в списке (будь то item
или sublist
) занимал уникальную позицию, за исключением значений position
, поступающих из таблиц items
и sublist_items
, при этом list_id
является общим.
ВОПРОС 2: Есть способ лучше организовать данные? Я знаю, что мне технически не нужен столбец sublist_id
в первой таблице, но я решил, что добавлю его, чтобы он позволил мне написать дополнительные ограничения, например «разрешить position
быть нулевым, только если sublist_id
не равен нулю».
🤔 А знаете ли вы, что...
SQL позволяет оптимизировать запросы с помощью индексов и подсказок (hints).
Что ж, это интересная проблема :)
Для этого может быть множество способов. По моему мнению, вы можете выполнить следующие шаги:
Sublists(Sublist_id,Name) (Sublist_id as PK)
Items(item_id,sublist_id,name) (Will have Sublist_id as not null foreign key from Sublist table) (item_id as PK)
Items_position(item_id,sublist_id, list_id,position) (you can add check constraint to check that combination of item_id and sublist_id should be available in Items table)(unique constraint for the combination of list_id and position)
Подсписки:
Sublist_Id Name
0 Dummy
1 Drinks
Предметы :
Item_Id Sublist_id Name
1 0 Apple
2 0 Eggs
3 1 Water
4 1 Orange
Items_Position:
Item_id Sublist_id List_id Position
1 0 1 1
3 1 1 0
2 0 2 1
4 1 3 1
Дайте мне знать, если я что-то упустил.