5

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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

#1 interesting idea. So each record would tell you which record should come after it if I understand correctly. I'm not sure how to best query/get the list displayed in the order with this method. Perhaps in a loop of the record it would read in the number for the next record to be shown and display it.
You need to make choices. You wrote you were unhappy about massive and slow updates, the linked list implementation would address it. It will come at a price that when you need to recreate the order (i.e. query) you would need to retrieve the whole dataset and programmatically iterate through it by always jumping to the next item. This is what linked lists are. With GraphDBs it is much easier, because there you just issue a 'traverse' statement to the DB and the records come back in the correct order! This is really a task for Graph DBs, which would also be blazingly fast at that.
Thanks i'll have to research Graph DBs as i've never heard or seen them in any articles or online until now. However if they require other software besides MySQL then I wont be able to use as my project is part of a module to be distributed to thousands running PHP and MySQL so I need to be able to use there existing server that runs the parent software for my module. Your idea of saving the next record onto each recoird is what interest me due to these limitations I have

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.