1. The position of an element in an array is not a property of the element but of the array.
2. Don't use sorted sets when you need the properties of an array / linked list.
You can store the todo list as an array of todos (use a user defined datatype), you can create another table called todo_list that contains an array of references to the todos. You can also create a linked list. Note that you'll have to use modern SQL to retrieve the linked list 'efficiently'.
The problem only becomes difficult when we dislike the obvious solutions because it violates some property of 'elegance' which apparently means restricting yourself to the 1980s versions of SQL implementations.
If the language we were writing this in was called JS and the code has the order of the array as a property of the object and we were continually sorting it every time we accessed the array instead of just reordering the array we'd recognize this as the anti-pattern it is. Similarly if for some reason using JS functions was considered 'inelegant', or for some reason using only JS features of 1996 instead of 2020.
Modern SQL has CTEs, functions, and arrays. They are very elegant when solving the problem of arrays in SQL.
A foreign key constraint in postgres requires that the entire column value in the foreign key match a corresponding column value of a key in the referenced domain table. Unless there have been quite recent extensions to the DDL in postgres, there is no foreign key constraint syntax to express a REFERENCES constraint over parts of a structured column. This is not supported for even the simple case of constraining one field in a UDT/composite type, much less the harder problem of constraining a variable cardinality set of elements in an array, or keys/values within a json/jsonb document.
You can try to emulate it with triggers, and start to appreciate the subtle complexities of the problem. For example, would we want a new class of element-wise actions to act analogous to ON DELETE/UPDATE CASCADE/SET NULL? Rather than pruning referenced row or referencing column value, you'd want to mutate just an element within the structured type, right? How would you expose these many choices if trying to design a new constraint syntax with reusable machinery?
You could also use the array to store only the order, and regular FK relationships for membership. That way the referential integrity of the items is guaranteed, but the order is more of a "best effort" approach, as in that there my be members that have no defined order (default to putting them last or first), or there may be items in the order array that have been deleted (and can then be ignored)
This is a much more practical answer. Point #1 seems exactly right.
Even without modern SQL arrays one could even keep references to place, per user, in a simple comma separated text field and munge it in code. Elegant? Probably not. But perhaps more practical then some of the approaches presented.
Comments
There are two problems here:
1. The position of an element in an array is not a property of the element but of the array.
2. Don't use sorted sets when you need the properties of an array / linked list.
You can store the todo list as an array of todos (use a user defined datatype), you can create another table called todo_list that contains an array of references to the todos. You can also create a linked list. Note that you'll have to use modern SQL to retrieve the linked list 'efficiently'.
The problem only becomes difficult when we dislike the obvious solutions because it violates some property of 'elegance' which apparently means restricting yourself to the 1980s versions of SQL implementations.
If the language we were writing this in was called JS and the code has the order of the array as a property of the object and we were continually sorting it every time we accessed the array instead of just reordering the array we'd recognize this as the anti-pattern it is. Similarly if for some reason using JS functions was considered 'inelegant', or for some reason using only JS features of 1996 instead of 2020.
Modern SQL has CTEs, functions, and arrays. They are very elegant when solving the problem of arrays in SQL.
Is there a way in PG to keep the foreign key constraints on an array of ids, or do you just have to give them up for practical reasons?
A foreign key constraint in postgres requires that the entire column value in the foreign key match a corresponding column value of a key in the referenced domain table. Unless there have been quite recent extensions to the DDL in postgres, there is no foreign key constraint syntax to express a REFERENCES constraint over parts of a structured column. This is not supported for even the simple case of constraining one field in a UDT/composite type, much less the harder problem of constraining a variable cardinality set of elements in an array, or keys/values within a json/jsonb document.
You can try to emulate it with triggers, and start to appreciate the subtle complexities of the problem. For example, would we want a new class of element-wise actions to act analogous to ON DELETE/UPDATE CASCADE/SET NULL? Rather than pruning referenced row or referencing column value, you'd want to mutate just an element within the structured type, right? How would you expose these many choices if trying to design a new constraint syntax with reusable machinery?
You could also use the array to store only the order, and regular FK relationships for membership. That way the referential integrity of the items is guaranteed, but the order is more of a "best effort" approach, as in that there my be members that have no defined order (default to putting them last or first), or there may be items in the order array that have been deleted (and can then be ignored)
As far as I know the FKs dont work with arrays but you can emulate them via triggers.
This is a much more practical answer. Point #1 seems exactly right.
Even without modern SQL arrays one could even keep references to place, per user, in a simple comma separated text field and munge it in code. Elegant? Probably not. But perhaps more practical then some of the approaches presented.
But modern SQL does have arrays.
Surprised to see the obvious solution at the bottom of the comments
This is interesting, when you say arrays, do you mean an array column-type? More details (or links) would be appeciated.
Array column type, use unnest to create a table that can be right? joined to the un-nested array. https://www.postgresql.org/docs/13/functions-array.html