Efficient Way to Store Reorderable Items in a Database

Efficient way to store reorderable items in a database for multiple users

You want an association/junction table. This would have at least three columns:

• UserId
• ItemId
• Ordering

(And you might have additional columns, such as an auto-incremented primary key and a creation dat.)

The first two should be foreign references to a users and items table respectively.

Best way to store ordered lists in a database?

A junction/link table with additional columns for the attributes of the association between movies and users is the standard way of realizing a many-many association with an association class - so what you have done seems correct.

Regarding the ease of insert/update/delete, you'll have to manage the entire association (all rows for the user-movie FKs) every time you perform an insert/update/delete.
There probably isn't a magical/simpler way to do this.

Having said this, you'll also need to run these operations in a transaction and more importantly have a 'version' column on this junction table if your application is multi-user capable.

Best way to rearrange order of db tables items?

In other words, the user can arrange the items in an arbitrary order, and that ordering needs to be persisted in the database?

As one commenter points out, there is no inherent ordering to rows in a database. Any retrieval that needs rows in a particular sequence needs to state the desired sequence via an ORDER BY clause.

For what you are suggesting, rather than incorporate a column to store the ordering information directly in your data table I would suggest a one-to-one join table with two columns - one being the primary key to the row, and a second used to store an integer that holds the user's sequence. This allows the sequence to change without actually modifying the rows themselves (preserving caches, etc.). For example, a table like:

CREATE TABLE UserSequence(
    key INT UNSIGNED,
    sequence INT UNSIGNED)

Where the 'key' column is a foreign key to the primary key of the table holding your data rows.

If you use largish numbers for the sequence (e.g., ordinal x 1000), you will leave space for users to create new items (or reorder existing items) with a priority somewhere in the middle of the sequence without renumbering all the rows.

Retrieval in sequence then is a simple matter of joining the UserSequence table in your query and ORDER BY the sequence column.

Also note, you could put a uniqueness constraint in place on the sequence column, which will allow the DB to tell you when you need to fall back and re-number items according to your chosen value for stride (e.g. 1000) rather than do a mid-sequence insert (where, for example, the sequence value is chosen via something like prev + (next - prev)/2). A foreign key constraint on the key column is also advisable.

Efficient and correct strategy for handling sortable items in a database

Describe the order in the database with an ordinal number, which starts at 0 for the first item and increases by 1 for every subsequent item. Then, you just need to send the ordinals of the moved item, and the ordinal of its new previous neighbour. You then do (i'm using $ to mark variables - you'll need to pass these into the queries from your code):

-- if $moved > $previous, and it's moving backwards, move everything between the new previous neighbour and the item forward one
update items
set ordinal = ordinal + 1
where ordinal > $previous
and ordinal < $moved;

-- else $moved < $previous, and it's moving forwards, move everything between the item and the new previous neighbour backwards one
update items
set ordinal = ordinal - 1
where ordinal > $moved
and ordinal <= $previous;

-- then move the item
update items
set ordinal = $previous + 1
where ordinal = $moved;

You could add a unique constraint to the ordinal column to help ensure integrity, but you'd have to be a bit cleverer about how you did updates.

How to keep ordering of records in a database table

I feel this is related to the general problem of using an array vs a linked list.
How about storing a foreign key referencing the next record in the same table? This is the linked list like approach.

For your example there are not too many tabs so an array based approach should work fine.
But for someone having hundreds of records it may be useful to use a self-referential foreign key.

ID Name      NExT  
 1 Home      2  
 2 About     3  
 3 Products  4 
 4 Shopping  NULL

Adding and deleting rows will be akin to linked list insertion and deletion.

Update:
Modified table

ID Name       NExT  
 1 Home       5  
 2 About      3  
 3 Products   4 
 4 Shopping   NULL
 5 Contact us 2

The order will be 1 > 5 > 2 > 3 > 4 as determined by the next column.
You can also use a prev column which will make it similar to a doubly linked list.

What's the best way to store sort order in SQL?

None of the answers so far have touched on the real problem with custom sort order and that is what happens when two different people want the same records sorted differently.

If you need a custom sort order, you need a related table to store it in, not an additional field. The table would have the userid, the recordId of the data and the sort order for the record. That way Joe Smith can have one order and Sally Jones another for the same data. Now you have the problem of new records being added to the data set. Do you put them at the beginning of the sort order or the end or do you require the person to set an order for them before they can be added to the set. This is in actuality a very complex problem that is generally not worth the amount of time it takes to implement because almost no one ever uses that system once it's in place (I mean do I really want to go through a hundred records and mark the individual order of each one?). Now it gets complicated in terms of saving the order of all the records (which will of course require changes the next time the query is run since there will be new records.) This is very painful process of limited untility.

I did this once in a proposal writing application because we needed to be able to sort the parts and tasks on the proposal in the order we thought would be most impressive to the customer. Even then, we had to institute a default order, so that they only need to move around the two or three things they really wanted to show up first instead of ordering 10,000 individual parts.

A better choice if you can get them to buy off on it, is to allow them to sort the data by columns (desc or asc). Usually the user interface can be designed so that if you click on a column header, it will resort the data by that column. This is relatively straightforward to do and meets most needs for custom ordering.

You really need to discuss this requirement with management and get details of how they want it to work beyond, I want custom ordering. This is often one of those things people think they want, but don't really use.

How to save a particular, mutable order into a database

The "naive" approach you suggest is also the best practice!

Best way to save a ordered List to the Database while keeping the ordering

FWIW, I think the way you suggest (i.e. committing the order to the database) is not a bad solution to your problem. I also think it's probably the safest/most reliable way.

---

Related Topics