Best way to save a ordered List to the Database while keeping the ordering

Question

I was wondering if anyone has a good solution to a problem I've encountered numerous times during the last years.

I have a shopping cart and my customer explicitly requests that it's order is significant. So I need to persist the order to the DB.

The obvious way would be to simply insert some OrderField where I would assign the number 0 to N and sort it that way.

But doing so would make reordering harder and I somehow feel that this solution is kinda fragile and will come back at me some day.

(I use C# 3,5 with NHibernate and SQL Server 2005)

Thank you

Answer 1

Ok here is my solution to make programming this easier for anyone that happens along to this thread. the trick is being able to update all the order indexes above or below an insert / deletion in one update.

Using a numeric (integer) column in your table, supported by the SQL queries

CREATE TABLE myitems (Myitem TEXT, id INTEGER PRIMARY KEY, orderindex NUMERIC);

To delete the item at orderindex 6:

DELETE FROM myitems WHERE orderindex=6;    
UPDATE myitems SET orderindex = (orderindex - 1) WHERE orderindex > 6;

To swap two items (4 and 7):

UPDATE myitems SET orderindex = 0 WHERE orderindex = 4;
UPDATE myitems SET orderindex = 4 WHERE orderindex = 7;
UPDATE myitems SET orderindex = 7 WHERE orderindex = 0;

i.e. 0 is not used, so use a it as a dummy to avoid having an ambiguous item.

To insert at 3:

 UPDATE myitems SET orderindex = (orderindex + 1) WHERE orderindex > 2;
 INSERT INTO myitems (Myitem,orderindex) values ("MytxtitemHere",3)