Storing orderred lists in an RDBMS

Created on 2024-04-22T16:58:27-05:00

Return to the Index

This card pertains to a resource available on the internet.

This card can also be read via Gemini.

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.