Let us say I've got a Product table inside a shopping site's database to help keep description, cost, e.t.c. of store's items. What's the most effective method to make my client in a position to re-order these items?

I create an Order column (integer) for sorting records but that provides me some head aches regarding performance because of primitive techniques I personally use to alter an order of each and every record following the one I really have to change. A good example:

Id    Order
5     3
8     1
26    2
32    5
120   4

Ok now what can one do in order to alter the order from the record with ID=26 to three?

Things I did was developing a saved procedure which inspections if there's any record in target order (3) and updates an order from the row (ID=26) otherwise. If there's an archive in target order the saved procedure executes itself delivering that row's ID with target order + 1 as parameters.

That triggers to update each and every record following the one I wish to switch to make room:

Id    Order
5     4
8     1
26    3
32    6
120   5

What exactly would a wiser person do?

  • I personally use SQL Server 2008 R2.
  • British isn't my native language. Sorry if there's any writing errors.
  • Thanks ahead of time.

Edit:

I want an order column of the item to become enough for sorting without any secondary secrets involved. Order column alone must specify a distinctive spot for its record.

Additionally to any or all, I question basically can use something similar to of the linked list: A 'Next' column rather than an 'Order' column to help keep the following products ID. However I have no clue crafting the query that retrieves the records with correct order. If anybody comes with an understanding of this method too, please share.

Update product set order = order+1 where order >= @value changed

Though with time you will get bigger and bigger "spaces" inside your order however it will still "sort"

This can add 1 towards the value being transformed and each value after it in a single statement, however the above statement continues to be true. bigger and bigger "spaces" will form inside your order possibly getting to the stage of exceeding an INT value.

Alternate solution given desire to have no spaces:

Make a technique of: UpdateSortOrder with parameters of @NewOrderVal, @IDToChange,@OriginalOrderVal

Two step process depending if new/old order is upgrading or lower the type.

If @NewOrderVal < @OriginalOrderVal --Moving down chain 

--Create space for the movement; no point in changing the original 
    Update product set order = order+1 
    where order BETWEEN @NewOrderVal and @OriginalOrderVal-1;

end if

If @NewOrderVal > @OriginalOrderVal --Moving up chain

--Create space  for the momvement; no point in changing the original  
  Update product set order = order-1 
  where order between @NewOrderVal and @OriginalOrderVal+1
end if

--Finally update the one we moved to correct value

    update product set order = @newOrderVal where ID=@IDToChange;

Regarding best practice most conditions I have experienced typically want something arranged by category and sorted alphabetically or according to "recognition on purchase" thus killing the necessity to give a user defined sort.

Merry Christmas or Happy Holidays if you want laptop computer version.

One solution I have tried personally previously, with a few success, is by using a 'weight' rather than 'order'. Weight being the apparent, the heavier a product (ie: the low the amount) sinks towards the bottom, the lighter (greater the amount) increases to the peak.

In case I've multiple products with similar weight, I suppose they're of the identical importance and that i organize them alphabetically.

What this means is your SQL will appear something similar to this:

ORDER BY 'weight', 'itemName'

hope that can help.

Make use of the old trick that Fundamental programs (among other areas) used: jump the amounts within the order column by 10 as well as other convenient increment. After that you can place just one row (indeed, as much as 9 rows, if you are lucky) between two existing amounts (which are 10 apart). Or move row 370 to 565 without needing to change the rows from 570 upwards.