Storing orderred lists in an RDBMS
Created on 2024-04-22T16:58:27-05:00
Include a "Sort Key" column where values are placed. When retrieving records the order is recovered by sorting on this field.
Changing order requires modifying sort keys.
Line numbers
Use increments of 100, 1000, and so on, so each new entry at the bottom is the highest number +1000. When moving an element between two other rows, use the numeric value between the two points. So to place a record between 1000 and 2000, assign the sort key of 1500.
Renumbering
Sometimes it is necessary to renumber the entire orderred list. This can happen when you run out of numbers to average between.
Renumbering requires pulling all the IDs and sort keys, rewriting all the numbers (ex. in stages of 1000), and inserting all of those numbers back in to the database.
Strings
CRDTs like to use strings since you never run out of space "between" identifiers. Identifiers just grow infinitely longer instead.