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

Leave gaps in the sequence or use a decimal rather than an integer data type.

Answer 2

As a user adds profiles, set each new profile's ordering number to the previous one +1000000. e.g. to start off with:

p1   1000000
p2   2000000
p3   3000000

When reordering, set the profile's order to the middle of the two it is going in between:

p1   1000000
p2   2000000
p3   1500000

This gives the order p1,p3,p2

Answer 3

How about using floating points for the order by column? This way, you can always squeeze a profile between two others, without having to change those two values. Eg if I want to place profile A between profiles B (ordervalue 1) and C (ordervalue 2), I can assign ordervalue 1.5 to A. To place it on top, where before the top used to have ordervalue say 1, you can use ordervalue 0.5

There's no reason to have integers for orderby and no reason to have increments of 1 between the order of profiles.

Answer 4

I think the idea of leaving gaps between the orders is interesting but I don't know if it a "more convenient" solution for your problem.

I think you would be better off just updating your order by column. Because you are still going to have to determine what rows the statuses have moved between, and what to do if two statuses are switched in position (Do you calculate the new order by value for the first one then the second one). What happens if the gap between isn't large enough?

It shouldn't be that data intensive to just enumerate down the order they put it in and update each record to the order.

Answer 5

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.

Answer 6

If the data set is small (which seems to be the case), I'd prefer to use a normal list of integers and update them in batch when a profile gets a new position. This better reflects the application functionality.

In Sql Server, for the following table User_Profiles (user_id, profile_id, position), I'd have something like this:

--# The variables are: 
--#   @user_id - id of the user
--#   @profile_id - id of the profile to change
--#   @new_position - new position that the profile will take
--#   @old_position - current position of the profile 

select @old_position = position 
from User_Profiles where 
user_id = @user_id and profile_id = @profile_id

update p set position = pp.new_position
from User_Profiles p join (
  select user_id, profile_id,
    case 
    when position = @old_position then @new_position 
    when @new_position > @old_position then --# move up
      case 
      when @old_position < position and 
           position <= @new_position 
      then position - 1
      else position
      end
    when @new_position < @old_position then --# move down
      case 
      when position < @old_position and 
           @new_position <= position 
      then position + 1
      else position
      end
    else position --# the same
    end as new_position
  from User_Profiles p where user_id = @user_id
) as pp on 
p.user_id = pp.user_id and p.profile_id = pp.profile_id

Answer 7

I think instead of keeping order in the orderby column you can introduce linklist concept to your design. Add column like nextId that will contain the next profile in the chain. When you query the profiles table you can sort out profiles in your code (java, C#, etc)