Hello. Faced with the task - there are many objects stored in the database table that the user can create in unlimited quantities. In addition, the user can assign them custom sorting, "dragging" elements. This sorting should be preserved and objects should be selected in accordance with it until the user changes it again.
Previously, the number of objects was limited, and, for these purposes, there was a column "sort", in which the number was written and samples from the database were sorted by this column. The main problem is sorting change, since I had to update all the records in the database (i.e. if I want the 4 object to become 8, I need to update the records about the objects 5-8, reducing their sorting by 1, and the object 4 to put down the sorting 8). To put it mildly, not the best option, but with a limited number was tolerated.
Now, according to business logic, you need to remove the restriction. Accordingly, a case is theoretically possible when the user wants to make 4 objects 100,000 and the system will have to make 99997 updates.
So far, I have not thought of anything better than creating a separate table where user_id and sort will be stored. In sort, store a json array of correctly sorted id elements. Thus, when requesting objects from the database, their sorting is first requested. Then, in accordance with it, the elements themselves are already id's. When changing the sorting - only one update request.
From the pros I see - you can refuse to use limits and offsets in database queries (I have all the id elements in the form of an array and the sample will go just by pk)
Of the minuses - theoretically a huge array that may not fit into the base column (in practice, of course, the user will never create such a number of objects - I think, in fact, the objects of the most active users will have no more than a few hundred). And also, the integrity of the data will have to be maintained at the application level, and not at the mysql level (when deleting an object, update the sorting itself, clearing the remote id from there).
Maybe someone can suggest the best solution. Thank you in advance.
