How do I store orders?

You may keep orders as literals, and use lexical sort:

1. A
2. Z

Add a task:

1. A
3. L
2. Z

Add more:

1. A
4. B
3. L
2. Z

Move 2 between 1 and 4:

1. A
2. AL
4. B
3. L

etc.

You update only one record at a time: just take an average letter between the first ones that differ: if you put between A and C, you take B, if you put between ALGJ and ALILFG, you take ALH.

Letter next to existing counts as existing concatenated with the one next to Z. I. e. if you need put between ABHDFG and ACSDF, you count it as between ABH and AB(Z+), and write AB(letter 35/2), that is ABP.

If you run out of string length, you may always perform a full reorder.

Update:

You can also keep your data as a linked list.

See the article in my blog on how to do it in MySQL:

In a nutshell:

/* This just returns all records in no particular order */ SELECT *
FROM t_list id parent
------- --------
1 0
2 3
3 4
4 1 /* This returns all records in intended order */ SELECT @r AS _current, @r := ( SELECT id FROM t_list WHERE parent = _current )
FROM ( SELECT @r := 0 ) vars, t_list _current id
------- --------
0 1
1 4
4 3
3 2

When moving the items, you'll need to update at most 4 rows.

This seems to be the most efficient way to keep an ordered list that is updated frequently.