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

Question

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:

1. 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.

2. 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?

Answer 1

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 :)