I have a table with unsorted rows and want to make them sortable by a column. I want the table to be sortable by a column, so I create a sort_by column.
The table now looks like this:
ID Data sort_by
-- ----- -------
27 Blahh 1
54 Blahh 2
57 Blahh 3
1 Blahh 4
Now I want to insert a row in second position (sort_by = 2). Then I'll have to update sort_by for all rows > 2 with sort_by += 1. That's a lot of updates if I have a few million rows.
So, is there a canonical way of doing this? Of course I could have larger steps between columns and insert new rows in the gaps etc, but is there an aesthetically pleasing way, i.e. not a hack?
The sorting is done manually by users. A good phrase to google for = upvote, everything I try turns up ways to sort table in some DBMS or some framework. :)
Two ways I can think of.
1) Change sort_by to a decimal. You will always[a] be able to insert a new row with more decimal places between existing values.
2) Turn your table into a linked list by replacing sort_by with comes_after_ID. This is a foreign key pointing to YourTable.ID.
ID Data Comes_after_ID
-- ----- --------------
27 Blahh NULL
54 Blahh 27
57 Blahh 54
1 Blahh 57
With the second design it would be easier to do the ordering in application code rather than SQL.
Either way a drag-and-drop GUI can hide this implementation from the user.
[a] Always(TM) is limited to the resolution of decimals on your system, of course. Eventually you will have to re-sequence your items. But this can be done as a monthly job rather than as the user processes input.