Ordering rows depending on user interaction

Question

Suppose I have a table of users. Something like:

ID integer,
USER text,
POSITION integer

My db tuples can be:

(1, "user1", 1);
(2, "user2", 2);
(3, "user3", 3);

My app lists all users and it has the possibility to reorder it. For example: You can make user3 go before user2.

(1, "user1", 1);
(2, "user2", 3);
(3, "user3", 2);

Also you might want to move someone to the first place. For example: if you have ...

(1, "user1", 1);
(2, "user2", 2);
(3, "user3", 3);

... and you move user3 to the first place, the db should be:

(1, "user1", 2);
(2, "user2", 3);
(3, "user3", 1);

Is using a POSITION column the correct approach? Is there a way to get this done without too many hits to the db?

Answer 1

Using the POSITION column as you suggest is perfectly valid. The number of DB reads will depend on the number of rows in the table what indexes you create.

If you were referring to saving the user made changes back to the DB, unless there are many changes to be submitted, simply emitting an UPDATE statement will suffice.

Answer 2

You can't do much better then what you are suggesting - to update the changed elements to the database each time you make a change. This can occasionally mean a large update for a small change (for example, adding an element at the start of a long list).

One minor improvement is to remove the restriction that the positions must be consecutive numbers starting from 1. You can instead allow that the positions could be 100, 200, 300 and use an ORDER BY to get the rows in the correct order. This way it is possible to insert a new item between two existing items without having to modify any of the other elements, at least until your sequence runs out of gaps and you need to "defragment" it.

But the extra effort to implement this system is probably not worth it, in my opinion. The simple solution works fine for most purposes.