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

I would consider a link table between the two items and (I think) this is a fairly industry-standard approach.

So you have a table called "User" and a table called "Items". You will want to create a third table called something like "UserItems".

This new table will have its own primary key but then contain two foreign keys; one to the User table and one to the Items table. As per your requirement you will also want to add a column to store the priority or sequence.

From the front end, when a user adds an item and gives it a priority you just add a row into the UserItems table. Reassigning the priority will update a row, etc, etc.

Answer 3

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.

Answer 4

Add to the items table a column which specifies their order. BTW, which DBMS you use?

Answer 5

This is how I would model it:

CREATE TABLE Application_Users
(
     user_id     INT NOT NULL,  -- Maybe do without this if the user_name is unique
     user_name   VARCHAR(20) NOT NULL,
     ...
     CONSTRAINT PK_Application_Users PRIMARY KEY CLUSTERED (user_id)
)

CREATE TABLE Widgets     -- Items is a really bad table name generally
(
     widget_id     INT NOT NULL,
     widget_name   VARCHAR(20) NOT NULL,
     description   VARCHAR(2000) NOT NULL,
     ...
     CONSTRAINT PK_Widgets PRIMARY KEY CLUSTERED (widget_id)
)

CREATE TABLE User_Widgets
(
     user_id     INT NOT NULL,
     widget_id   INT NOT NULL,
     ranking     SMALLINT NOT NULL,
     CONSTRAINT PK_User_Widgets PRIMARY KEY CLUSTERED (user_id, ranking)
)

The PK on User_Widgets is debatable. If they can only have one instance of a particular widget in their queue at one time then you could theoretically put the PK on (user_id, widget_id). If they can have ties for ranking then you might put the PK over all three columns. It depends on the business requirements.

If you do include the ranking in the PK, keep in mind that it may make moving rankings for items more difficult. You would usually fix the problem by deleting and inserting changes rather than updating rows. For example, to swap ranks 4 and 5 you would delete both rows, then add the items back in with the corrected rankings. Personally I don't think that this is a bad thing, but you need to keep it in mind.

Answer 6

This should do it; the order belongs to the relationship between user and item. If an item can belong to one user only, place an additional unique constraint on the ItemID in UserItem.