How should I store sorted items in a database?
activerecord, database, database-design, relational-database, ruby-on-rails
Solution
It's not really hard to update all those rows in your example with a couple of SQL statements. You don't need to fire 11,000 updates at your DBMS (which I assume is what you were trying to say).
First, update all the books that are being shuffled forward one position:
UPDATE book
SET position = position + 1
WHERE position < 11000
AND position >= 1
...and then set the position of the book you're moving:
UPDATE book
SET position = 1
WHERE id = whatever
Problem
In my application, users can rearrange their favorite books in whatever order they choose. I have a "books" table in my database with a row for each book. Currently, there's an integer column called "position" that stores the position of each book: 1 for the top book, 2 for the next one, etc. The problem is that if someone drags a book from, say, position #11000 to position #1, I then have to make 11,000 updates to the database. This seems inefficient. Is there a better way to do this? One idea I've had would be just to have another table called "book_sort_orderings" or something, with a row for each user. And one column would be a huge text column that stores a sorted list of book ids. Then when the user rearranges the books, I can pull this value out into my code, perform the rearrangement there, and update the database row. Of course, any time a book is added or deleted I'd have to update this array as well. Is this the "right" way to go about things? Or is there something clever I can do to speed things up without changing my current setup?