I am trying to see what is the best way to handle the following scenario
I have a table called, lets say User, and i have a table called items. Each user can have multiple items added to his account. I have another table , lets say AssociateItem, which maintains the association between user and the items. (links UserID to ItemID). A user can have multiple items, so there is a one to many relationship between User and Items. The order in which these items are displayed is important, and the user change the order from UI(Kind of like Netflix Queue :)). So i need a way to save the order in somewhere, lets say preference table. so for each user i can save the display order.
I thought about a simple XML for saving the order. Lets say a user has items 1, 3 and 5 in his account, and display order is 3, 5, 1. I can maintain a xml and change it everytime user changes his display preference
You could add an ordering number on the AssociateItem table. You could then retrieve the items ordered by this ordering number.
TABLE AssociateItem(UserId, ItemId, SortNumber) SELECT * FROM User U INNER JOIN AssociateItem AI ON U.UserId = AI.UserID INNER JOIN Item I ON AI.ItemId = I.ItemId ORDER BY AI.SortNumber
The messy part comes when you want to move the items around as you'll want to ensure that the numbers are kept in sync.
If SortNumber is an integer then you'll want to update all the SortNumbers of the following Items to number + 1.
You may be able to cheat and avoid all these updates by using real numbers and ranking the new item at the mid point between the previous and the next item SortNumbers.
e.g. If you want to insert something at position 2 of the sequence
0,1,2,3 you could assign it the number 0.5 giving
0, 0.5, 1, 2, 3
If you then want to insert something new at position 2 you could assign it as 0.25 giving
0, 0.25, 0.5, 1, 2, 3 etc. Obviously this will stop working at a point when you don't have enough precision to represent the number properly but it should be fine for smallish lists.