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
