Looking for solution to persist rows sequence in a database table that allow efficient reordering at runtime

Question

I have a database table. There is a field name "Sequence" that indicate sequence when user presents the rows in report or grid visually.

When the rows are retrieved and presented in a grid, there are few UI gadget that allow user to reorder the rows arbitrary. The new sequence will be persist in database table when user commit the changes.

Here are some UI operations:

1. Move to first
2. Move to last
3. Move up 1 row
4. Move down 1 row
5. multi-select rows and move up or down
6. multi-select rows and drag to new position

For operation like "Move to first" or "Move to Last", it usually involve many rows and the sequence those rows would need to be updated accordingly. This may not be efficient enough at runtime.

It is a common practice to use INTEGER as sequence's data type. Other solution is using "DOUBLE" or "FLOAT" that could overcome the mass update of row sequence but we will face problem if we reach the limit of precision.

Answer 1

What about storing the sequence as a linked list in the database? Your table would have some sort of unique ID column, and a next_id column.

When the user reorders rows, the only updates you need to issue are for rows that with different next_id pointers.

Another option is to use a floating point sequence column and have a server-side process that "cleans up" the sequences periodically by renumbering them back to whole numbers. Add a "dirty bit" to the sequence so that you're not reading data that doesn't need cleaned up.

Answer 2

If you're worried about how long it's going to take to update N rows then it makes me wonder how many rows are in the grid. It sounds like your talking about thousands if not tens of thousands. If that's the case you might want to rethink how you're presenting the information.

Having said that. it's quite common to use an integer value to maintain order. It should also be quite fast to update hundreds (if necessary) of records.

Lastly, if performance is a concern you could move the saving out to a background process. Unless you have a reason that requires to wait for everything to update.

Answer 3

I assume you want to use minimum number of SQL/Database UPDATE-s when you change a record. For example, if you move just one record up or down, you'd want to just do one update, not multiple updates.

You probably already thought of this algorithm since you mentioned floats, but I'm going to explain it any way so I can comment on INTEGER vs FLOAT: I'd use INTEGER values to keep the sequence information. When the first element is inserted into the database I'd give it the sequence number 0. The next record would have a sequence number of 100 (not 1) and then the next one 200. In the end sequence number would be integer numbers, negative or positive.

Sample strutcture:

1. sequence -100
2. sequence 0
3. sequence 100
4. sequence 200
5. sequence 300

Operations using record "3" (has sequence number 0).

Move first: New sequence will be sequence(1)-100 = -200. Move Last: New sequence will be sequence(5)+100 = 400. Move Up: Try to find an sequence number between records (1) and (2): new sequence = -50 Move Down: Try to find an sequence number between the records (4) and (5): new sequence = 250

Any of those operations might fail: Moving "first" might fail if we end up with a number smaller then MinInt, move last might fail when we go over MaxInt. If that happens it's probably time to do an mass update and give new sequence numbers to every record in the database.

Moving up or down might fail if the previous/following 2 records have consecutive numbers: in that case you can swap sequence numbers, so you end up doing just 2 updates.

INTEGER vs SOMETHING ELSE: In the end it's all about bit count. An 64 bit float number doesn't buy anything compared to an 64 bit integer but it does give you more problems: maybe the back end database stores the float in a different way and it looses precision. Id' just stick to INTEGERS.