I am in the process of creating a GIS application with multiple layers and many pieces of content shapes on each layer. Currently each layer/shape is drawn in the order that it was returned from the database, but I want to give the user some control over the ordering. So the user should be able to drag and drop layers/shapes up and down in the layer tree, which should change the drawing order.
In order to persist these changes, each layer/shape must have som sort/ordering index in the database. However, if I use integers for the sorting key, whenever a layer/shape is sorted into a new position, this would require updating all of the following rows with +1.
Is there a good way to avoid this?
The reason I would like to avoid this issue is that there might potentially be a lot of content (thousands of shapes) in a layer, and I am concerned about the performance impact of updating that many rows. Furthermore, it's just not a very elegant solution.
I have considered using floating point values instead, which would make it possible to simply create a new value between two existing values, but at the risk of eventually running into precision problems.
Another approach I am considdering is storing the sort order as a kind of linked list, where each row points to the previous or following row, which would reduce the required number of updates, but would make construction of the tree in the GUI more complex. Even worse, if not all shapes/layers are displayed at all times, creating the layer/shape tree would become impossible.
Is there a commonly accepted good solution for this kind of problem?