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.
Comments
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.
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.
Just make sure there's no competing edits going on at the time that might cause a race.
That's what a transaction is for.
Edits are done by the UI. You do transactions in your UI?
I do transactions for sensitive edits that are triggered from the UI. The UI itself doesn't have a database connection though.