[eluser]jedd[/eluser]
[quote author="NateL" date="1259900636"]
My first thought is ID, Name, Position
[/quote]
Never done it, as sort order has always been such an ephemeral thing - something I'm happy to determine at the time I call the DB, rather than something I want each user to be able to modify back into the database.
Two methods seem feasible - the first, you've described, where you have an order field in the DB. The second is (effectively) a doubly-linked list that you maintain in the DB. Neither seems particularly elegant, and so presumably (hopefully) there are better algorithms out there.
The big problem with the position method is that if you bring the last item to the front of the list, you have to modify every row in your table. This is expensive (and a bit messy).
The big problem with the linked list is that you're kind of replicating what the database should be doing for you anyway - though as I say, I can't think of other patterns for this problem. Moving one item around means that you have, worst case, 5 rows to modify (very rough on-fingers calculation).
Quote:The tricky part I'm trying to avoid is if I swap the first two (John's position goes from "1" to "2") - how will i tell Julie's position to go from "2" to "1" ?
If that doesn't happen, then I have two items at position "2" - which will cause some ugly conflicts.
This bit seems quite easy - you just check for whatever position you're moving to, for the extant item in same (the row with that position number) and change both. Or, more accurately, change all items that will be affected. Swapping items #1 and #2 obviously means only two rows get changed.
