How to Store Ordered Items Which Often Change Position in Db

How to store ordered items which often change position in DB

If you relax the constraint that the Position column must contain integers from 1 to N and instead allow it to contain any numbers then you can do both searches and updates efficiently.

You can insert an item between two other items with position A and B by calculating the average (A + B) DIV 2. For example if A is 10000 and B is 12000 then your new position is 11000. Occasionally you will run out of gaps due to clustering, at which point you can run through the whole table redistributing the positions more evenly.

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.

How to store a checklist with ordered items on the DB

The answer is so simple, it's embarrassing.
I was approaching the problem from the wrong angle, and getting stuck.
Rather than thinking of the problem as "separate checklist to then merge", it's WAY easier to see it as "one big checklist where items might be filtered out".

This way, I can simply apply the usual "one weight per field defining the records' order" (as it's always done when you have an ordered set); then, I will simply "filter out" some fields that are not applicable. The filtering can happen in a number of ways, but that's beyond the scope of my question.

Once facing the problem from the right perspective, it's becomes very simple and straightforward.

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.

Maintaining/updating record order in mysql

Doing this sort of thing for e.g. sales orders with line numbers maintained by the user, I've found it best to handle it in an array in the BL or UI. Usually they will want to adjust several records, and sometimes want to say "forget it". So the easiest might be to just wait until they hit the "OK" button (or your equivalent) and then write them all back with current ordering.

You may end up dealing with deletions, too, which is another problem you can handle the same way.

storing rows order in mysql

I think you'll be better off just storing the ordering position in a dedicated field, instead of trying to implement a linked list.

The issue with the linked list is that is requires some sort of list traversal to "reconstruct" the order before you can display it to the user. Normally, you'd employ a recursive query to do that, but unfortunately MySQL doesn't support recursive queries, so you'll either need to fiddle with stored procedures, or end-up making a database round-trip for each and every list node.

All in all, just updating the order field of several rows from time to time (when you need to reorder) is probably cheaper than traversing the list every time (when you need to display it), especially if you mostly move rows by small distancees. And if you introduce gaps (as you already mentioned), the number of rows that you'll actually need to update will fall dramatically, at the price of increased complexity.

You may also be able to piggy-back the order field onto the clustering mechanism offered by InnoDB.

YMMV, of course, but I'd advise benchmarking the simple order field approach on representative amounts of data before attempting to implement anything more sophisticated...

---

Related Topics