Suppose I've some objects, and that i want the consumer to have the ability to reorder them by any means they want, say, by dragging them around. So I'd have

  • cheese
  • muffins
  • milk

and so the user drags 'milk' to the peak, making the brand new order

  • milk
  • cheese
  • muffins

It is possible to best practice how you can keep order of those objects inside a database? The naive approach would most likely be to simply store a statistical value known as "order" for every object, but this appears like an excessive amount of hassle in my experience, because you'd need to shuffle an order-values around more often than not.

The "naive" approach you suggest can also be the very best practice!

Taking Tony Andrews' answer into account, you can alternatively store a "next" index with every entry. When you pull all of them in, walk the array by using the chain. Which makes moving a product simpler, while you just touch maximum two rows.

The disadvantage with this particular approach is should you ever require a subset (e.g. the very first 3 products) you'll still have to pull in most the products, or make use of a SQL loop. Therefore it is between affecting all rows throughout update, or being able to access all products throughout read. As always, appraise the speed and find out what's best for the situation.

Searching at Tony Andrew's and Mark's solutions particularly, it appears I truly only have two options:

  • Saving a 'next' value, making the objects become a linked list (see Mark's answer)
    With this particular, altering an order is affordable, but I'd need to retrieve the products and then sort them by their 'next' value, that is costly
  • Saving an 'order' value (see Tony Andrew's answer)
    This will make locating cheap but saving a brand new order potentially costly, because within the worst situation, I'd need to change all of the order values. cletus highlights that certain can use a significant number as 2^n for that order multiplier.

Meta: Many of these solutions are great and proper, which must i choose as correct?

During my programs read procedures are likely to happen have sex than creates. Opt for the statistical value to point sort order and cope with the price of reordering the products. This really is a lot more than composed for because you are able to effectively retrieve the products within the correct order for display reasons (which inside a typical application happens more turning does).

Also, as already pointed out, should you retrieve a subset from the data (filter on type or something like that) the rest of the products continue to be within the proper sort order.

Recall the mantra K.I.S.S.

If you wish to ask them to redisplayed within the same order AND you would like them to have the ability to reordered anytime I do not think you will get from storing some value that signifies display priority within the database. I have used the approach you describe to buy products within an FAQ, scientists connected having a grant, an order of products inside a menu, ...

Yup, inside a relational database there's no order, it's among the fundamental concepts. So there's just not a way with no statistical value or something like that like this.