How to store sorted items in a database?

Question

In my application, users can change their favorite books in any order.

I have a "books" table in my database with a row for each book. Currently, there is an integer column called "position" in which the position of each book is stored: 1 for the top book, 2 for the next, etc.

The problem is that if someone drags a book, for example, to position # 11000 to position No. 1, I have to make 11,000 updates for the database. This seems ineffective. Is there a better way to do this?

One of my ideas would be to have another table called "book_sort_orderings" or something else, with a row for each user. And one column will be a huge text column that stores a sorted list of book IDs. Then, when the user reorders the books, I can output this value to my code, perform a regrouping there and update the database row. Of course, at any time when the book is added or deleted, I will have to update this array. Is this the “right” way to do things? Or is there something clever that I can do to speed up the process without changing my current setting?

Answer 1

It is not very difficult to update all of these lines in your example with a few SQL statements. You do not need to run 11,000 updates in your DBMS (what I assume is what you were trying to say).

First, update all books that are shuffled into one position:

UPDATE book SET position = position + 1 WHERE position < 11000 AND position >= 1 

... and then set the position of the moving book:

 UPDATE book SET position = 1 WHERE id = whatever 

Answer 2

You will be surprised how quickly a decent DBMS can update 11,000 rows, assuming that you are doing this in a "mass" way (as opposed to creating a separate round-trip database for each row).

But if you want to avoid this, use the old BASIC trick (from the time that BASIC still had line numbers): leave spaces!

Instead of using positions: 1, 2, 3, 4, 5 etc... use 10, 20, 30, 40, 50 etc...

So, when you need to move (say) the first element closest to the last place, just change 10 to 41 and you will get: 20, 30, 40, 41, 50 etc... Obviously, you will need to play a little if the gap is completely filled, but this strategy should be able to almost eliminate massive UPDATEs.

---

Another possibility is to implement a doubly linked list: instead of ordering, save the identifier of the previous and next elements. Reordering can be done simply by "reconnecting" the identifiers, as in a list in memory. Unfortunately, you would also not allow the DBMS to sort elements directly (at least without inconvenient and probably inefficient recursive queries) - you will have to perform sorting at the application level, so I would recommend it again

---

And one column will be a huge text column that stores a sorted list of book IDs.

Please do not do this. You violate 1NF , and very good reasons do not, including data consistency and (you will have to rewrite the entire field for any single change to any part of it).

Answer 3

Your current solution does not seem to work for multiple user settings. If the book order is set in the Book table, will it not be permanent for all users?

As others have noted, it is usually best to maintain data normalization, which will require you to add another table, as you suggest. That way you can have a new BookOrdering table. This way it will have the columns book_id , user_id and position . Thus, for each user and each book there is an assigned position.

This will set the default order (which will not be saved in this table), but users will be able to change the order. Only default changes will be recorded in the table. When you want to load custom books, you first need to check this table for a specific user_id, and then shift / adjust the order accordingly.

Answer 4

1. Another idea:

identify the new varchar column (orderCode) in the list table. This column stores the order for each element in char format, as shown below:

 '1','2','3','4','5','6', '7', '8', '9', '91', '92',.... 

Now, if you want to insert an element between 1 and 2, the orederCode value will be "11". And the order of the list will be:

 '1','11','2','3','4','5','6', '7', '8', '9', '91', '92',.... 

Now suppose you insert between 11 and 2, you insert the value orderCode = '12':

 '1','11','12','2','3','4','5','6', '7', '8', '9', '91', '92',.... 

Finally, if you want to insert an item from "11" and "12", the order value will be: "111":

 '1','11','111','12','2','3','4','5','6', '7', '8', '9', '91', '92',....'99','991,... 

you can run a query from time to time to commit values ​​of the order of one char.

2. The best solution:

use floating point data type instead of varchar. So this list:

 '1', '11', '111', '12', '2', '3', '4', '5', '6', '7', '8', '9', '91', '92',.... 

will look like this:

  0.1, 0.11, 0.111, 0,12, 0.3, 0.4, 0.5, 0.6, ......,0.9,0.91,.... 

defiantly, floating point sorting is faster than varchar sorting.