How to design table that can be re-sequenced?

The obvious answer to me is to use the last solution you mentioned but with decimals (floats).

So you start with, say: {0.1, 0.2, 0.3, 0.4, 0.5}. If you move the last item to between 0.2 and 0.3 it becomes 0.25. If you move it to the top it becomes 0.05. Each time you simply take the mid-point of the two numbers on either side. In other words, the average of the previous/next items.

Another similar solution is to use characters, then sort by strings alphabetically. Starting with {1, 2, 3, 4, 5}, if you move the 5 between 2 and 3 you'd use 25. If you do a string sort of the list you keep the right order: {1, 2, 25, 3, 4}.

The only problem I can think of with these methods is that eventually, you will hit the limit of floating point precision, i.e. trying to find a number between 0.0078125 and 0.0078124. A few ways to solve this:

• Run a script every so often that runs through every item and reorders them to {0.1, 0.2, 0.3, ...}.
• Don't use two decimal places when you can use one. Between 0.2 and 0.25 you could use 0.23 instead of the calculated 0.225.
• Re-sequence locally, not globally. If you have {0.2, 0.3, 0.6} and want to insert after 0.2, you could set the second one to 0.4 and insert the new item at 0.3.

The ID and Sequence/SortOrder are separate and should not depend on each other at all.

for a move-up/move-down feature:You can swapping Sequence/SortOrder Values

or

For a drag and drop feature:

1) Establish the new Sequence/OrderNumber for the selected record.

2) Get the selected records current sequence, then Update the selected record with the new number.

3)a) If the New Sequence number is below the current sequence number increment all the sequence numbers for the records that have a sequence number >= the new sequence number (exlcuding the selected one)

b) if the New sequence number is above the current sequence number, decrement all the sequence numbers below the new selected one and above the current one.

Hope this makes sense and I have though it out the right way (below is the actual implementation).

I have implemented this in a single SQL statement that has some small amount of logic, not for the purists, but its works well.

Here is an example (OP: you will want to change the GUID IDs to INTs):

CREATE PROCEDURE [proc_UpdateCountryRowOrder]    @ID UNIQUEIDENTIFIER,    @NewPosition INTASSET NOCOUNT ONDECLARE @CurrentPosition INTDECLARE @MaximumPosition INTIF (@NewPosition < 1) SET @NewPosition = 1SELECT @CurrentPosition = [Countries].[Order]FROM [Countries]WHERE [Countries].[ID] = @IDSELECT @MaximumPosition = MAX([Countries].[Order])FROM [Countries]IF (@NewPosition > @MaximumPosition) SET @NewPosition = @MaximumPositionIF (@NewPosition <> @CurrentPosition)BEGIN    IF (@NewPosition < @CurrentPosition)    BEGIN        BEGIN TRAN        UPDATE [Countries]        SET [Countries].[Order] = [Countries].[Order] + 1        WHERE [Countries].[Order] >= @NewPosition        AND [Countries].[Order] < @CurrentPosition        UPDATE [Countries]        SET [Countries].[Order] = @NewPosition        WHERE ID = @ID        COMMIT TRAN    END    ELSE    BEGIN        BEGIN TRAN        UPDATE [Countries]        SET [Countries].[Order] = [Countries].[Order] - 1        WHERE [Countries].[Order] <= @NewPosition        AND [Countries].[Order] > @CurrentPosition        UPDATE [Countries]        SET [Countries].[Order] = @NewPosition        WHERE ID = @ID        COMMIT TRAN    ENDENDGO

How about linked lists? :-)

CREATE TABLE item(    id INT PRIMARY KEY,    prev INT,    next INT);WITH RECURSIVE sequence AS (    SELECT item.id, item.prev, item.next FROM item    WHERE item.prev IS NULL  UNION    SELECT item.id, item.prev, item.next FROM sequence    INNER JOIN item ON sequence.next = item.id)SELECT * FROM sequence;

Actually, I don't have a PostgreSQL on hand to test if this actually works (and MySQL doesn't support SQL-99's WITH RECURSIVE), and I don't seriously recommend it either.