Database Design – Update All Rows When Sorting Value Changes for Single Row

database-designperformancesortingupdate

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?

Best Answer

I've never handled anything like this before but why not do a combination of the +1 and the decimal versions. Do the decimal versioning while they are editing .. then at the end of an editing session (when they do a final save) go ahead and re-write all of the values as integers. Presumably in any given editing period there won't be so many edits that you will run into major problems with precision.

Another option would be to use a list in a single column in say the project table. Basically have your IDs listed there along these lines.

,43,23,14,55,27, etc.

Then you can use string manipulation to remove ,55, from where it is and stuff it between 23 and 14. In SQL Server the T-SQL code might look like this:

DECLARE @OrderString varchar(500) = ',43,23,14,55,27,'
DECLARE @MoveString varchar(50) = '55'
DECLARE @MoveBefore varchar(50) = '14'

-- Remove the value to be moved
SET @OrderString = STUFF(@OrderString, PATINDEX('%,'+@MoveString+',%', @OrderString), LEN(@MoveString)+2,',')

-- Put it in the right place
SET @OrderString = STUFF(@OrderString, PATINDEX('%,'+@MoveBefore+',%',@OrderString),0,','+@MoveString)

-- Print it out
PRINT @OrderString
Related Question