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
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.