sql - 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.

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.