Create ordering in a MySQL table without using a number (because then it's hard to put something in between)

Question

I have a long list of items (say, a few million items) in a mysql table, let's call it mytable and it has the field mytable.itemid.

The items are given an order, and can be re=ordered by the user by drag and drop. If I add a field called mytable.order and just put numbers in them, it creates problems: what if I want to move an item between 2 other items? Then all the order fields have to be updated? That seems like a nightmare.

Is there a (scalable) way to add order to a table that is different from just giving every item a number, order by that, and do loads of SQL queries everytime the order is changed?

Answer 1

You can either create a trigger or stored procedure to resequence the order values appropriately, or drop all the associated records and insert new ones in the new order.

Answer 2

You can use fractions (two integers, i.e. a/b). Always there is at least one rational between two other.