The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database row order problem

I have a table that contains rows that need to be displayed to the user in a specific order.

The User also has the ability to drag and drop the rows to change that order. To implement this I can include a "SequenceNo" field that contains an integer that the query can sort on. This number is updated when the User reorders the rows.

This is fine, but if the user moves a row a significant "distance", then I would have to modify all of the sequence numbers in between.

I'm assuming this is a common problem, is there a standard method to solve it?

The best method I can come up with is to give consecutive sequence numbers a large delta when generated. So, when a row is dropped, it can be given a sequence number somewhere in between it's 2 nearest rows? - and therefore not need to change any other rows?
IanH. Send private email
Wednesday, April 09, 2008
premature optimization?

The first implementation of using a simple ascending integer and updating rows as necessary is probably the best way - it may not be efficient but it's simple. If you always have a small number of rows you won't even notice the additional updates.

The second implementation will be much more complicated. You need algorithms for spacing the integers, determining the new integer to insert, and you will still have to handle the situation where there is no space between integers.

So unless there is compelling performance / concurrency issues always choose the simplest method.
DJ Send private email
Wednesday, April 09, 2008
still vulnerable to concurrency issues, but a different way to approach it is to store a reference to the next and previous row for sorting purposes. this is harder to manage for display purposes, but easier for update purposes.

for example

row 3, prev row=NONE, nextrow=1
row 1, prev row=3, nextrow=5
row 5, prev row=1, nextrow=2
row 2, prev row=5, next row=4
row 4, prev row=2, next row=NONE

if you want to move 2 to the end, you change row 5's

nextrow to be row 4
row 4's next row to be 2 and prev to be 5
row 2's next to be NONE and prev to be 4

this means that regardless of the number of items in the list, you only need to make 3 changes to rows.

doing it this way would be very situational, though, for obvious reasons.

Don Dickinson Send private email
Wednesday, April 09, 2008
Have you considered using real numbers for your sequence numbers?  That way you can intercalate a new number between any two existing entries,  limited only be the precision of your real numbers.

Once in a blue moon you could resequence all the numbers,  but this would beat updating a lot of rows every time a user moves a row around.

Wednesday, April 09, 2008
This strikes me as premature optimisation.

Realistically, how large is the 'distance' a user might move any given row?  Thousands of rows or somewhere in the dozen or two they can see on-screen? 

If the former, DnD seems to be a poor choice for the user's experience.  If the latter, the user won't notice anything from 'dumb' row updates assuming a relatively modern database.

As DJ points out, unless there's a good reason for complexity, simpler is usually better.
a former big-fiver Send private email
Wednesday, April 09, 2008
Just use the simple system until somebody actually complains about performance. Unless your table is REALLY HUGE (hundreds of millions of rows), there is no way that this update will take more than a couple seconds (probably not even that). Consider that, when updating the sequence number field, your update command looks like this (for the case where the item is moved from a lower sequence number to a higher one):

  update MY_TABLE set SEQ_NO = SEQ_NO-1

That's going run pretty damn fast, especially if you have indexed properly on SEQ_NO.
Jeffrey Dutky Send private email
Wednesday, April 09, 2008
Thanks for the replies.

Perhaps it is premature to worry about it. It just struck me that this seemed like the sort of problem the database people would have a standard solution for.

It is possible that it could be a couple of thousand rows. To handle the GUI the table will be split (like how MS Office implements the split function)

Perhaps it isn't too big a problem. I was imagining looping through a dataset modifying the values one at a time, but Jeffrey's SQL example makes it blindingly obvious that there is a much simpler way.

I had thought of Don's linked list idea, but this falls down when I want to perform the SELECT statement and display the table in the correct order.

I like the cunning real numbers solution though ;-)
IanH. Send private email
Wednesday, April 09, 2008
I've got a SQL Server 2005 based system with the same type of scenario. 

Users can drag lead classifications around and alter their weight.  The system is set with an interval of 10 between each of the rows by default.  The row dragged by the user is set to 5 + the lower weight of the two rows it's dropped between.  And that value and the ID are passed to the stored proc. 

The dragged row is updated to its new "5" weight and then I run this statement

update lead_classification set [weight] = nw.new_weight from
        (row_number() OVER (ORDER BY [weight]))*10 as new_weight,
        lead_classification_id  from lead_classification) nw
        where lead_classification.lead_classification_id = nw.lead_classification_id

so basically I just generate a new set of weights starting at the lowest and reset the whole sequence.
TetonSig Send private email
Wednesday, April 09, 2008
I worked on a system that did this the hard way and it was fine. You just do a binary chop on each move and re-number them if you don't have a space.

It was a garment range management system for a large UK retailer (M&S) and the Users wanted to be able to drag stuff around in the UI, to move garments from one category to another, or move the categories about.

I agree that it was overkill though and just updating the display sequence numbers would have been fine. I was assigned the task of re-writing a lot of the code in this application when I joined the team, and found lots of interesting stuff like this.

I'm not sure if the guy who wrote the original stuff was a "genius" who liked to over-engineer everything, or was just bored of working on a mundane application and wanted to spice it up a bit. It all worked fine, and was well documented, but I just got the impression that a KISS approach would have been better, and certainly would have made my job easier.
Odysseus Send private email
Thursday, April 10, 2008
Actually this reminds me of programing in Basic on an Apple II...

To allow new code lines to be inserted between existing ones it was normal to use line numbers like:


Allowing new lines at 110, 120 etc. and order would be maintained.
Thursday, April 10, 2008
The big problem with updating all the row numbers is that one day, one of the records will be locked.
Vaughan Send private email
Tuesday, April 29, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz