Best way to allow the User to define a Table’s Order?

Question

Best way to allow the User to define a Table’s Order?

We are using SQL Server 2005 and DevExpress controls.

We have a table that contains the following:

1. Process A
2. Process B
3. Process C
4. Report A
5. Report B
6. Report C

We want to allow the user to change the order to anything they want.

Here is one example:

1. Process C
2. Process A
3. Process B
4. Report B
5. Report A
6. Report C

To accommodate this we added a DisplayOrder (INT) field to the table that is maintainted by the our application.

Is using an INT field the best solution for a user defined order?

Are there any other methods to accomplish this?

The reason, I am asking, is in our current application it takes about 1 second to move a row down (or up). I am about to crack open the code to see why it is taking so long and if you Stack Overflow gurus have any good ideas, I might implement them at that time.

If you are curious, here is how I believe our application allows editing of the DisplayOrder:

1. Load the table into a GridView
2. Select a row
3. Clicking a Move Down button (there is also a Move Up button)
4. The Click Event will swap DisplayOrder of the current row with the row underneath it.
5. The change, to both of the records, are written back to the database
6. This takes about 1 second per click (i.e. 10 clicks equal 10 seconds)

Answer 1

Could it be taking 1 second per exchange because you are updating the DB with each click? Maybe even reselecting the data again too?

Why not select the data into local objects. Bind to those objects in the grid. When you move the objects up and down adjust their display order property. Then only do an update to the DB

1. When the user finally clicks Save.
2. If any of the values of the object have changed.

Answer 2

I would move the saving decision into an explicit Save button for the re-ordering because the user may change their mind and, as Peter Morris suggests, that will get past your performance problem.

Further things to consider - explicit Move to Top and Move to Bottom items.

I implemented a very successful UI using this kind of approach for user-driven selection of searchable items and reporting items, in an enterprise system. More details of the UI are available for you to see (be kind, remember it's about 15 years old - back in flat mono days!). One key refinement from a lot of user testing - the sweet spot for saving this order was not per-user or per-company but per-group. Groups were people working together who had a the same starting page (essentially the same root in a massively connected graph). We had about 7 unique groups in the organisation.

So, if you go for a per-user or per-group approach, you need to pull the sort data out into a separate table which maps data keys to ordinal integers.

Answer 3

If you area talking about a display order that the user wants to set and keep, that needs to be in a separate table with the userID and the ids of the records he or she wants to see and a display order column. Then the query that gets the data to that page would usea join to the user preference table.

If you want to just set the order temporaily for the time the person is looking at the screen, do it thorugh the user interface.

In any event do not reorder the records in the base table, that is a poor choice as you will run into race conditions where user a wants one setting and user b inthe meantime is trying to change to a differnt setting.