Alternative methods for storing list sort order in JavaScript and MySQL

Question

I have a project task list using JavaScript and a jQuery collation library to make it easier to sort draggable task list items.

When changing the sort order, the order is stored in the MySQL database. Currently, if there are 100 items in the task list, and item number 2 moves to position 3. This will update the 2-100 records in the database using the sort_order column in the DB table of the task record.

This, obviously, is not the most effective solution, because it can simultaneously update a huge number of database records.

Another problem is that the date_modified column that I have in my task records will be updated when nothing from the task changes except for the sort order number, which can be called from a different position of the record shift. Obviously, there are ways around this, and I am currently encoded in a huge complex SQL query that does not update my date_modified column unless other table columns are changed.

Therefore, the reason for this post is the study of other alternative methods of storing my sort order on a large volume of records.

One of my ideas is to create a new DB table task_sort_order , which can have task_id and sort_order . This table will then contain an entry for each individual task entry. The advantage of this method is that my SQL will be simplified, since I don’t have to worry about the task date fields being updated when only the sort order is changed. However, simultaneous updating of the same number of records is still required. In my original example, there will still be a massive update of 99 records out of 100 in 1 query. It seems that I have advantages over my current one.

Are there other ways to improve the storage of a large number of sort orders in a database?

I saw some people who will also save each sorting sequence number in increments of 10 or another number, so if, for example, there are 5 entries 1=10, 2=20, 3=30, 4=40, 5=50 , and record number 2 moved to position 4. It will have a new look of the order 1=10, 3=30, 4=40, 2=44, 5=50 , so in this example only record number 2 will have the sort_order value, changed and saved in the database.

This seems rather complicated as many records start to move, and you get all kinds of strange odd numbers, and he also needs to somehow calculate the correct number to make sure that the new moved item does not contradict the previously transferred item.

As you may seem, I'm open to exploring any ideas for other methods or even ways to improve my current method?

Answer 1

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

• You can try to implement the concept of a linked list in your relational database schema, i.e. for the item you are storing a "link" to the next item. Thus, you only need to update a few records for each rearrangement.

• You can use graphical DBs such as Neo4j or OrientDB, where a linked list will be just one of the possible data entry relationships that DBs support natively.