I've got a assortment of objects inside a database. Images inside a photo gallery, items inside a catalog, sections inside a book, etc. Each object is symbolized like a row. I wish to have the ability to randomly order these images, storing that ordering within the database then when I display the objects, they'll be within the right order.

For instance, let us say I am writing a magazine, and every chapter is definitely an object. I write my book, and set the sections within the following order:

Introduction, Ease of access, Form versus. Function, Errors, Consistency, Conclusion, Index

It is going towards the editor, and returns using the following recommended order:

Introduction, Form, Function, Ease of access, Consistency, Errors, Conclusion, Index

How do i store this ordering within the database inside a robust, efficient way?

I have had the next ideas, but I am not thrilled with them:

  1. Array. Each row comes with an ordering ID, when order is transformed (using a removal then an insertion), an order IDs are up-to-date. This will make retrieval easy, becasue it is just ORDER BY, however it appears simple to break.

    // REMOVAL
    UPDATE ... SET orderingID=NULL WHERE orderingID=removedID
    UPDATE ... SET orderingID=orderingID-1 WHERE orderingID > removedID
    // INSERTION
    UPDATE ... SET orderingID=orderingID+1 WHERE orderingID > insertionID
    UPDATE ... SET orderID=insertionID WHERE ID=addedID

  2. Linked list. Each row includes a column for that id from the next row within the ordering. Traversal appears pricey here, though there might by a way to make use of ORDER BY that I am not considering.

  3. Spread array. Set the orderingID (as utilized in #1) to become large, therefore the first object is 100, the second reason is 200, etc. When an insertion happens, you simply put it at (objectBefore + objectAfter)/2. Obviously, this will have to be rebalanced from time to time, so you do not have things too close together (despite floats, you'd eventually encounter rounding errors).

None of those appear particularly elegant in my experience. Does anybody have an easy method to get it done?

I'd perform a consecutive number, having a trigger up for grabs that "makes room" for any priority whether it already is available.

When the objects aren't heavily keyed by other tables, and also the lists are short, removing my way through the domain and merely re-placing the right list may be the simplest. But that is improper when the lists are large and you've got plenty of constraints to decelerate the remove. I believe the first method is usually the cleanest. Should you run it inside a transaction you can be certain nothing odd happens while you are in the center of the update to ruin an order.

Some other alternative could be (in case your RDBMS supports it) to make use of posts of type array. Although this breaks the normalization rules, it may be helpful in situations such as this. One database that we learn about which has arrays is PostgreSQL.

Only a thought thinking about option #1 versus #3: does not the spread array option (#3) only postpone the issue from the normal array (#1)? Whatever formula you select, either it's damaged, and you will encounter issues with #3 later, or it really works, after which #1 should work equally well.

Make use of a floating point number to represent the positioning of every item:

Item 1 -> .

Item 2 -> 1.

Item 3 -> 2.

Item 4 -> 3.

You can put anything between every other two products by simple bisection:

Item 1 -> .

Item 4 -> .5

Item 2 -> 1.

Item 3 -> 2.

(Moved item 4 between products 1 and a pair of).

The bisection process can continue almost indefinitely because of the way in which floating point amounts are encoded inside a computer.

Item 4 -> .5

Item 1 -> .75

Item 2 -> 1.

Item 3 -> 2.

(Move item 1 towards the position soon after Item 4)