database - Saving order preference in SQL -

I am trying to see what is the best way to handle the following scenarios

I have a table called, the user says, and I have a table called items. Each user can have many items in his account. I have another table, which tells that the associate item, which maintains the connection between the user and the objects. (Link to ItemID for UserID) Users can have many items, so there are many relationships between users and items. The order in which these items are displayed is important, and the order changes from the user UI (like the Netflix queue). So I need to save the order in some place, say the priority table. So I can save the display order for each user.

I thought about a simple XML to save the order. It is said that there are items of 1, 3 and 5 in the user's account, and the display order is 3, 5, 1. I can maintain an XML and change it when the user changes his display preference

  & lt command & gt; & Lt; Items & gt; 3 & lt; / Item & gt; & Lt; Items & gt; 5 & ​​lt; / Item & gt; & Lt; Items & gt; 1 & lt; / Item & gt; & Lt; Order & gt;  

One of the ruthless ways of doing this seems to be that I am not a database expert, so if I can give a better solution I really appreciate it. Sorry if the scenario is not very clear

You can add a serial number to the AssociateAIT table. You can then retrieve the items ordered by this Ordnance number.

For example

  select Table Associate Item (UserID, Item ID, Sort Number) * Join User U Inner on Associate ITIMU U.UserId = AI .UserID INNER IIT IIT IIT IID IIT IIT IIT IIT IRI From the SRT number,  

the part of the mess comes when you want to move the objects around as you would like to ensure that the number is placed in sync

< P> If sorting is an integer, then you want to update all sortings of the following items in the number + 1.

You can cheat and SortNumbers by using real numbers for all these updates and ranking new items between the previous and next item at the middle point.

If you want to insert something in position 2 of sequence 0,1,2,3 , you can give it 0.5 number 0, 0.5, 1 , 2, 3

If you want to insert something new in position 2, then you can give it as 0.25 0, 0.25, 0.5, 1, 2, 3 etc. Obviously this will stop working at a point when you do not have enough precision to display the numbers properly, but for a small list it should be okay.