user171034 user171034 - 3 months ago 17
SQL Question

Saving order preference in SQL

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.

pjp pjp
Answer

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.