Keep order of items in table

Question

everyone! I'm making a simple todo app. I stopped on the one problem. I want to allow users to change the order of elements in a list (saving this to database).

One of first idea was:

Create a column (order) and change it every time when user do something. It's good when we have a few records, but what with bigger number? My thought:

id | name | order
1  | lorem| 1
2  | ipsum| 2
3  | dolor| 3

When user change "dolor" to first position, script must update all of records. This isn't the best solution I think. Anyone can share the knowledge how to optimize that? I will be grateful!

[Using a sort order column in a database table](http://stackoverflow.com/questions/8607998/using-a-sort-order-column-in-a-database-table) — SqlZim, Mar 17 '17 at 20:37
Possible duplicate of [Using a sort order column in a database table](http://stackoverflow.com/questions/8607998/using-a-sort-order-column-in-a-database-table) — philipxy, Mar 17 '17 at 20:59

KIKO Software · Answer 1 · 2017-03-17T20:40:10.327

0

You could use a column called next or previous. This is called a linked list, or if you use both, a double linked list. See:

https://en.wikipedia.org/wiki/Doubly_linked_list

Moving a record up one step in a database table would involve two steps:

Remove the record from the order.
Insert the record back into the order.

In all you would always need about five record changes for a double linked list, and a minimum of three records for a linked list.

edited Mar 17 '17 at 20:40

answered Mar 17 '17 at 20:35

KIKO Software

15,283
3
18
33

This is not a SQL'ish solution. – Gordon Linoff Mar 17 '17 at 21:45

score 0 · Answer 2 · answered Mar 17 '17 at 21:48

If you want to store this data in a database, then an "ordering" column is appropriate.

Whenever you update or insert into the table, you will need to update this column (deletes are unnecessary). In general, you will need to update all the rows after the changed row. A trigger can do this work.

Cycling through rows is probably fine for a few dozen or even a few hundred rows (depending on how powerful your database is). So, depending on the length of the list, this is likely to be fine.

Any enhancements depend on additional factors. Some that I can think of:

How large will the lists really be?
What kind of transformations are most import? (Swaps? Inserts? deletes? updates?)
Will the transformations happen in bulk?
Will multiple users be changing the list at the same time.

Keep order of items in table

2 Answers2