Efficient way to store reorderable items in a database [closed]

For a drag and drop interaction, the better bet is a priority. You would start with the priorities being 1, 2, 3, and so on, just like a sort order.

But then, the user wants to move item 5 between 1 and 2. Voila! Give it the value of 1.5. No other values need to change. The index update takes care of the rest.

For this to work, the priority needs to be stored as a floating point number. That might be an issue. Also, a sufficiently large number of changes could result in pushing the limits of floating point. So, if a user tries to take the last element and insert it between the first two, s/he can get away with it about few dozen times or so.

You can fix this with a process that periodically reassigns number for one (or all users, if in batch) starting at 1.

If you don't need to be able to manipulate someFkRef across users (for instance, getting the list of users interested in something), then you could have only one record per user, with an ordered list of someFkRef (refA, refB).

But it's a form of de-normalization, and as it has some drawbacks, it really depends on your needs (and your future needs, that is where comes the trouble)

Not sure what your dependent references might be to the ID field, but have you thought about over-writing it? I think there is a SET IDENTITY INSERT = ON, or some such that you can do.

I realize this is an odd thing to suggest, but considering what you're trying to do, it might make sense, adn cause the least amount of overhead.