Looking for solution to persist rows sequence in a database table that allow efficient reordering at
- by Chau Chee Yang
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:
Move to first
Move to last
Move up 1 row
Move down 1 row
multi-select rows and move up or down
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.
