I'm attempting to see what's the easiest method to handle the next scenario
I've got a table known as, allows say User, and i've got a table known as products. Each user might have multiple products put into his account. I've another table , allows say AssociateItem, which keeps the association between user and also the products. (links UserID to ItemID). A person might have multiple products, so there's a 1 to a lot of relationship between User and Products. An order by which these products are displayed is essential, and also the user alter the order from UI(Type of like Netflix Queue :)). And so i need a method to save an order in somewhere, allows say preference table. so for every user i'm able to save the display order.
I figured in regards to a simple XML to save an order. Allows say a person has products 1, 3 and 5 in the account, and display order is 3, 5, 1. I'm able to conserve a xml and alter it each time user changes his display preference
<order> <item>3</item> <item>5</item> <item>1</item> <order>
It appears a clumsy method of carrying this out. I am not a database expert. therefore if someone can provide a much better solution. i'll really be thankful. Sorry when the scenario is not so obvious.
You could include an ordering number around the AssociateItem table. You can then retrieve the products purchased with this ordering number.
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 untidy part comes when you wish to maneuver the products around as you will want to be sure that the amounts are stored synchronized.
If SortNumber is definitely an integer then you will want to update all of the SortNumbers from the following Products to number + 1.
You might have the ability to cheat and steer clear of each one of these updates by utilizing real amounts and ranking the brand new item in the mid point between your previous and subsequently item SortNumbers.
e.g. If you wish to place something at position 2 from the sequence
0,1,2,3 you can assign it the amount .5 giving
0, 0.5, 1, 2, 3
Should you then wish to place new things at position 2 you can assign it as being .25 giving
0, 0.25, 0.5, 1, 2, 3 etc. Clearly this can cease working in a point when you do not have enough precision to represent the amount correctly but it ought to be acceptable for smallish lists.
1) To reply to your question, it can save you an order within the association table. Add an "OrderNumber" area together with "userId" and "itemId". Maintain that area just like you alluded to using the xml. You will find merely a couple of specific instances where you would like to maintain data in xml inside a SQL driven web application which is undertake and don't. Bare this information within the database for consistency and speed.
2) This seem like a many-to-many relationship not really a one-to-many. You managed to get obvious that certain user might have many products, to ensure that informs me its among individuals options. The rest of the real question is is one able to item fit in with many clients? If that's the case, its a many-to-many and also you require the AssociateItem table. If, however, the items are only able to fit in with one client, then you've a 1-to-many relationship and you do not need the AssociateItem table. Rather add userId towards the products table as well as add the brand new "OrderNumber" area straight to the products table. Just something to consider.