Alternative methods of storing a list sort order in JavaScript and MySQL

Question

I have a Project Task list using JavaScript and the jQuery Sortable library to allow easy drag and drop sorting of the the Task list items.

When the sort order changes, the order is saved to a MySQL database. Currently if there are 100 task list items and item number 2 is moved to position 3. This would make records 2-100 all be updated in the database with a sort_order column on the task record DB table.

This is obviously not the most efficient solution since it can cause huge amounts of DB records to be updated at once.

Another issue is this will cause the date_modified column I have on my task records to all be updated when nothing on the task has changed except a sort order number which could be caused from other records shifting position. Obviously there are ways around this and I currently have coded in a huge complex SQL query which makes sure to not update my date_modified column unless other columns on the table are modified.

So the reason of this post is to explore other alternative methods of storing my sort order on large volume of records.

One idea I have is to create new DB table task_sort_order which could have the columns task_id and sort_order. This table would then hold a record for every single Task record. The benefit of this method is that my SQL would be simplified since I would not have to worry about a Task record date fields being updated when only the sort order has changed. It would however still require the same amount of records to be updated at once. In my original example it would still be mass updating 99 records out of 100 in 1 query. It does seem to have benefits over my current though.

Are there any other ways to improve upon storing large numbers of sort orders to a database?

I have seen some people who will also save each sort order number in increments of 10 or another number so that if for example there is 5 records 1=10, 2=20, 3=30, 4=40, 5=50 and record number 2 moved to position 4. It would have a new sort order of 1=10, 3=30, 4=40, 2=44, 5=50 so in this example only record number 2 would have its sort_order value changed and saved to the database.

This seems rather complex as many records start getting moved around and you end up with all sorts of weird odd numbers and it would also have to somehow calculate the correct number to make sure a new moved item is not conflicting with a previously moved item.

As you can seem I am open to hearing any ideas for other methods or even ways to improve my current method?

Answer 1

I think your problem lies in the shortcomings of how you store the order in the DB. Two ideas:

1. You can try to implement the concept of a linked list in your relational DB schema, i.e. per item you store a "reference" to the next item. In this way you will only need to update a few records per rearrangement.

2. You can use graph DBs such as Neo4j or OrientDB where a linked list would be just one of possible data entry linkages that the DBs support natively.