Saving order preference in SQL

Question

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

<order>
<item>3</item>
<item>5</item>
<item>1</item>
<order>

It seems a clumsy way of doing this. Im not a database expert. so if someone can give a better solution. i will really appreciate it. Sorry if the scenario is not very clear.

Answer 1

You could add an ordering number on the AssociateItem table. You could then retrieve the items ordered by this ordering number.

e.g.

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.

Answer 2

Two points:

1) To answer your question, you can save the order in the association table. Add an "OrderNumber" field along with "userId" and "itemId". Maintain that field just as you alluded to with the xml. There are only a few specific instances where you would want to maintain data in xml in a SQL driven web app and this is not one of them. Keep this information in the database for consistency and speed.

2) This look like a many-to-many relationship not a one-to-many. You made it clear that one user can have many items, so that tells me its one of those options. The remaining question is can one item belong to many clients? If so, its a many-to-many and you need the AssociateItem table. If, however, each item can only belong to one client, then you have a one-to-many relationship and you don't need the AssociateItem table. Instead add userId to the items table and also add the new "OrderNumber" field directly to the items table. Just something to think about.