1

I have a shopping list application where users can drag and drop an arbitrary number of items into a list. Items are organized in two-dimensional order, where each one has an integer position, i.e. indexes. As is common with this domain, no two items can have the same position.

This is currently represented in the following table:

  id    list_id    name    position
------------------------------------
  1        1       apple      0
  2        1       banana     1
  3        1       orange     2
  4        2       milk       3

list_id is a foreign key to another table (lists).

This table has the following unique constraint:

ALTER TABLE public.items
    ADD CONSTRAINT unique_position 
      UNIQUE (list_id, position)
      DEFERRABLE INITIALLY IMMEDIATE;

This helps maintain data integrity, and prevents scenarios where a bug in the application (or a naughty user) causes two items to be inserted in the exact same position. (The reason it is DEFERRABLE is that the constraint needs to be deferred until the end of a transaction containing multiple UPDATE position statements.)

Now I want to add a feature whereby the user can also create sub-lists inside the main list and insert the items in those sub-lists, similarly in a sequential fashion.

My plan is to add a sublist_id column to the original items table:

  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

And have that reference a second table called sublists:

  id    list_id   name   position_in_list
------------------------------------------
  1        2     drinks         3

And then connect the two tables using a third table called sublist_items:

  id    sublist_id   item_id   position
----------------------------------------
  1        1            7          0
  1        1            8          1

The issue with this is that the items table will list some item positions as NULL, since those items are now inside a sublist, and it is sufficient to keep track of the position of the sublist inside the main list, and the positions of the items inside the sublist.

QUESTION 1: How can the original constraint be changed such that it is applied across two tables? In other words, I still want each item in the list (whether it is an item or a sublist) to occupy a unique position, except the position values coming from both items and sublist_items tables, with list_id being the commonality.

QUESTION 2: Is there a better way to organize the data? I know I technically don't need the sublist_id column in the first table, but I figured I would add it so that it would allow me to write additional constraints, e.g. "allow position to be null only if sublist_id is not null".

Ege Ersoz
  • 6,461
  • 8
  • 34
  • 53
  • Do you have flexibility to change database design? – codeLover Aug 16 '18 at 05:44
  • 1
    Not sure if I understood everything correctly. I would keep both the list and the sublist position in `items`, the `sublist` table would then only contain additional attributes like the name. The constraint could then be on `list_id, position, coalesce(sublist_id, -1), coalesce(sublist_position, -1)`. – Laurenz Albe Aug 16 '18 at 06:07
  • Smells like tabbing-order, again ... https://stackoverflow.com/q/51837519/2235885 Here, the new key is composite {list_id, position}, but that does not really matter. – joop Aug 16 '18 at 12:27
  • @GarimaGupta Yes, I have total control over the database design. – Ege Ersoz Aug 16 '18 at 15:30
  • @LaurenzAlbe I thought about your suggestion more. With that design, is it safe to assume that the `items` list can contain a row for each sublist denoting the sublist's `position` in the main list? And then the `sublists` table would have an `item_id` foreign key pointing to the `id` of the sublist item inside the `items` table? – Ege Ersoz Aug 17 '18 at 05:13
  • 1
    It's not totally normalized, yes. I thought of a design where `sublist` is mostly unnecessary and everything (except additional information like the sublist name) is kept in `items`. – Laurenz Albe Aug 17 '18 at 05:23
  • @LaurenzAlbe The modified constraint you provided gives a syntax error. `UNIQUE (list_id, position, coalesce(sublist_id, -1), coalesce(sublist_position, -1))` – Ege Ersoz Aug 17 '18 at 21:45
  • Right. You cannot do that with a constraint, but you can use an index: `CREATE UNIQUE INDEX ON items (list_id, position, coalesce(sublist_id, -1), coalesce(sublist_position, -1));` – Laurenz Albe Aug 18 '18 at 04:09
  • That's too bad, because it needs to be deferrable in order to support bulk updates. And unique indexes can't be deferred. Anyway, I found another solution. Thank you for your help. – Ege Ersoz Aug 18 '18 at 06:29

1 Answers1

0

Well it is an interesting problem :)

There can be numerous ways to do it. As per me you can folllow the following steps:

  1. Create a dummy sublist_id (purely for technical purpose for the items which do not have any sublist_id).
  2. Normalize your existing tables as :

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. Now you can start filling the data as :

Sublists : 

Sublist_Id         Name
    0              Dummy
    1              Drinks

Items : 

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

Let me know in case I am missing anything.

codeLover
  • 2,571
  • 1
  • 11
  • 27