Условные уникальные ограничения

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

В настоящее время это представлено в следующей таблице:

  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).


1
67
1

Ответ:

Что ж, это интересная проблема :)

Для этого может быть множество способов. По моему мнению, вы можете выполнить следующие шаги:

  1. Создайте дурачок sublist_id (чисто для технических целей для элементов, у которых нет sublist_id).
  2. Нормализовать ваши существующие таблицы как:

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)

  1. Теперь вы можете приступить к заполнению данных как:

Подсписки:

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

Дайте мне знать, если я что-то упустил.