The surprisingly difficult problem of user-defined order in SQL

Re:The surprisingly difficult problem of user-defined order ... by smolder on Friday January 15, 2021 @10:09PM (#25799658)

Any of these methods can be combined with a process that re-indexes the order column once things get too pathological, or even does it on a schedule.

[ Parent | on HN ]

Re:The surprisingly difficult problem of user-defined order ... by ivan_ah on Saturday January 16, 2021 @12:59AM (#25800433)

Yeah float sort order field + some "DB maintenance" script seems like it would solve this.

To be fair though, the scenario of inserting 38 items in a row in the same location (the fail mode of Approach 2) does sound like something that could happen in one user session, so might need another check of some sort in addition to the weekly check.

Come to think of it, 38 inserts in the same location might be a good test case to add for any sortable data model.

[ Parent | on HN ]

Re:The surprisingly difficult problem of user-defined order ... by recursive on Saturday January 16, 2021 @12:09AM (#25800221)

Just make sure there's no competing edits going on at the time that might cause a race.

[ Parent | on HN ]

Re:The surprisingly difficult problem of user-defined order ... by tehjoker on Saturday January 16, 2021 @02:30AM (#25800758)

That's what a transaction is for.

[ Parent | on HN ]

Re:The surprisingly difficult problem of user-defined order ... by bboreham on Saturday January 16, 2021 @03:24AM (#25800927)

Edits are done by the UI. You do transactions in your UI?

[ Parent | on HN ]

Re:The surprisingly difficult problem of user-defined order ... by recursive on Saturday January 16, 2021 @11:19AM (#25803195)

I do transactions for sensitive edits that are triggered from the UI. The UI itself doesn't have a database connection though.

[ Parent | on HN ]