I ran into this problem and instead of solving it the proper way I took the lazy route by adding a unique constraint on the column and when it threw an errror in the insert I just added one more extra letter!
It's a neat idea, but this edge case makes it seem a lot like the "arbitrary precision" version of Option 2 except slightly worse on all counts. You're taking up more storage and the inserts are a lot less elegant.
Also, what about when you want to initialise a list with more than 30 elements? Do you get to "z" then continue with "za", "zb"... ?
Comments
Say you insert three items. a: 1, b: 2, c: 3.
Then you reorder 3 after 1. The new order is a: 1, aa: 3, b:2.
Then you reorder 2 after 1. The new order is a: 1, aa: 2, aa: 3.
But since there are now two values with the same sort key, it might as well be a: 1, aa: 3, aa: 2.
Did I misunderstand your solution or is that a bug?
I ran into this problem and instead of solving it the proper way I took the lazy route by adding a unique constraint on the column and when it threw an errror in the insert I just added one more extra letter!
It's a neat idea, but this edge case makes it seem a lot like the "arbitrary precision" version of Option 2 except slightly worse on all counts. You're taking up more storage and the inserts are a lot less elegant.
Also, what about when you want to initialise a list with more than 30 elements? Do you get to "z" then continue with "za", "zb"... ?
Here is a StackOverflow Q&A about something just like this: https://stackoverflow.com/questions/38923376/return-a-new-st...
A similar system is used in Jira called LexoRank