What would be the best way to store records order in SQL

Question

I have a table of users profiles. Every user can have many profiles and the user has the ability to arange the order of how they will be displayed in a grid.

There are 2 tables Users and Profiles (1:M)

I've added a orderby column to the Users table where will be values like 1,2,3..

So far it seems to be okay. But when a user will change the order of the last record to be the first I have to go throught the all records and increment their values +1. This seems to me pretty ugly.

Is there any more convenient solution for this kind of situation ?

Answer 1

The best solution is one which mirrors functionality, and that's a simple list of integers. Keeping the list in order is only a few SQL statements, and easier to understand than the other solutions suggested (floats, gapped integers).

If your lists were very large (in the tens of thousands) then performance considerations might come into play, but I assume these lists aren't that long.