Saving order in SQL

Question

I am trying to see how best to handle the following scenario

I have a table called, allows the user to speak, and I have a table called elements. Each user can add multiple items to their account. I have another table, say AssociateItem, which maintains a relationship between the user and the elements. (UserID links to ItemID). A user can have several elements, therefore there is a one-to-many relationship between the User and the Elements. The order in which these items are displayed is important, and the user changes the order from the user interface (sort of like Netflix Queue :)). So I need a way to save the order somewhere, say, a preference table. so for each user I can keep the display order.

I was thinking of simple XML to save order. Suppose a user has positions 1, 3 and 5 in his account, and the display order is 3, 5, 1. I can maintain xml and change it every time the user changes his or her screen preference.

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

This seems to be a clumsy way to do this. I am not a database expert. therefore, if someone can give a better solution. I'll be very grateful. Sorry if the scenario is not very clear.

Answer 1

You can add the order number in the AssociateItem table. Then you can get the items ordered by this serial number.

eg.

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

, , , .

SortNumber , SortNumbers + 1.

, , SortNumbers.

. - 2 0,1,2,3, 0,5, 0, 0.5, 1, 2, 3

- 2, 0.25, 0, 0.25, 0.5, 1, 2, 3 .. , , , .

Answer 2

:

1) , . "OrderNumber" "userId" "itemId". , xml. , xml -, SQL, . .

2) " ", " ". , , . , ? , --, AssociateItem. , , , " ", AssociateItem. userId , "OrderNumber" . .

Answer 3

:

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)
)

PK User_Widgets . , PK (user_id, widget_id). , PK . -.

, , . , , . , 4 5, , . , , .

Answer 4

( ), .

, "" "". "UserItems".

, ; - User - Items. .

, , UserItems. .. ..

Answer 5

, . BTW, ?

Answer 6

That should do it; order belongs to the relation between userand item. If an item can only belong to one user, add an additional unique restriction on ItemID in UserItem.