Best way to support reordering html table rows using jquery as well as make SQL backend fast?

asp.net-mvc, jquery, sql-server

Solution

For the frontend look at jQueryUI's Sortable feature. It's normally applied to `<ul/>` or `<ol/>` elements, but a quick Google Search turns up guides to adapting it for `<table/>` elements too, if needed.

The backend issue of one insert cascading into many updates can be solved by treating it as a linked-list.

I'm assuming you're storing your data as:

Rank | Key
1    | Zebra
2    | Pig
3    | Pony

So inserting a new row could require updating potentially every existing row:

Rank | Key
1    | Penguin * Inserted
2    | Zebra   * Updated
3    | Pig     * Updated
4    | Pony    * Updated

Instead, store their order by relating them to another row in the table.

Key | ComesAfter
Zebra | null
Pig   | Zebra
Pony  | Pig

So inserting a new row will require at most one update.

Key     | ComesAfter
Penguin | null    * Inserted
Zebra   | Penguin * Updated
Pig     | Zebra   * Unchanged
Pony    | Pig     * Unchanged

Of course you can use IDs for this

However

You might be prematurely optimizing; it could be that some simple SQL work could prove to be quite efficient even while issuing many updates. Consider this:

Key   | Rank
Zebra | 3
Pig   | 1
Pony  | 2

Notice that the Value is acting as a natural key here, it could also have an ID (which is different from the `Rank`).

To add a new row as Rank 1 you'd need to do something like:

begin tran
  insert values ('Penguin', 1)
  update table set Rank = Rank + 1 where Rank >= 1
commit

Adding indexes and possibly partitions (depending on the size of the table) will offer great performance improvements--the only tradeoff being a bit more occasional DB administration.

The main question here is which aspect you reuqire mose: fast reads, fast writes, or simple design? You can have two of the three :)

Problem

I have an asp.net-mvc website and I have an html table of data and one of the columns is ranking which represents the priority of that row (each row represents a request). I allow people to edit the data in a row with a new number but it doesn't actually affect any of the other rows (for example, nothing is stopping folks from entering a value that alraedy exists in another row) I am looking for a solution to have a more effective front end and a fast backend. I will break down each: - Front End: I want some functionality where I can either drag and drop rows up and down or click up/down arrow or when i enter a ranking value in a row it updates the ranking of all other rows. I basically don't want the user to have to update every row if there is a new entry that is ranked #1 (basically having to update previous 1 => 2, and previous 2=>3, and previous 3=>4 etc . . Are there any jquery libraries or useful patterns that help support this functionality (to avoid having to wire it all up from scratch) as this seems like generic functionality that i can imagine many other people using. For reference, if anyone is familiar with the JIRA Greenhopper Planning board, this would be ideal as an example to mimic. - Back End My related question is that when i go update the backend, i am trying to figure out how to avoid this being really slow operation. If there is a rank field in my database table and someone updates an item to be number one, i essentially have to run an update query on EVERY other row to change the value to the existing value + 1. Is there some pattern to do this more effectively?

Original source