Reordering an ordered list

Question

I have a following SQL table with data

ProductList 
id  order productname
79   1     name1
42   2     name2
67   3     somename
88   4     othername
99   5     XYZ
66   6     ABC

Display order is very volatile, it will change frequently, users will add or remove items and reorder the items.

How should i handle this situation without updating multiple records. Example: if user enters a new product between 1 and 2 order, i do not want to update the order of all the records beneath 2 and if someone switch order 3 to 4 i don't want to update every record under 3.

Also, how should i design the history table to keep order history.

Answer 1

Use the 'orrible old trick made famous(?) by old BASIC coders - set your orders to be 100, 200, 300, 400 etc. and then you can pick an order 'inbetween' when you need to. This could get messy - and if you're anticipating a lot of reordering then I'd recommend that you have a scheduled task to 'reorder' the order values every now and then for the entire table.

Answer 2

You have two options:

• Iterate over the rows, using an UPDATE statement with a function/etc to generate the updated order value
• Scorched Earth: You delete the existing records, and insert identical ones save the corrected order value

There's no SQL functionality to make this easier, and no real option that is simplistic.

Answer 3

You could instead use a linked list for ordering (with some special way to identify the head).

Insertions are one INSERT and one UPDATE. Deletions are one DELETE and one UPDATE. A move is three UPDATEs. (Use transactions to ensure the linked list doesn't break, of course.)

Answer 4

Add a DATETIME column called OrderDateTime. Use this column (in descending order) to resolve "ties" in ordering, and only update it when an ordering operation takes place.

For instance, in your example, assume all the rows have an OrderDateTime value from yesterday.

Now, to insert an item between 1 and 2, you'd set it's Order value to 2 and it's OrderDateTime value to now. When you SELECT * FROM ProductList ORDER BY Order ASC, OrderDateTime DESC the new number 2 item will sort before the existing one.

Similarly, to swap items 4 and 5, you'd update item 5 to have an order of 4 and an OrderDateTime of now. It would become a more recent 4 item and appear earlier.

You need to watch out, if you try to insert an item between two other items that already have the same Order value, that you split the OrderDateTime value difference.

Answer 5

A second answer to the same question:

Use a DOUBLE field for ordering and split the difference between the two values you want to insert between. In any standard business application, I doubt very, very much whether you'll ever approach the number of inserts beyond which you cannot resolve the sort order difference.

Answer 6

You can use a string in place of a number of infinitely many digits. If you want to insert between "1" and "2" make it a "15".