StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>It looks like what you have in your requirement is to at the very least store a userID, username and a dynamic list of items for a user. You would need a schema which can hold the necessary information you need. A simple schema can allow for very fast retrieval and complex schemas can have a lot of integrity built in. There are pro's and con's for both different designs, and depending on what your requirement is, one schema design would work better than another.</p> <p>Here are some examples that you can consider (user - item schema relationship):</p> <p><strong>many - many relationship schema</strong></p> <ul> <li>requires that a user to have at least one item list</li> <li>if xml format changes many updates will be needed</li> <li>users can have the same items as other users</li> <li><p>will get you a list of items for a user without the need of any joins</p> <p><strong>User</strong><br> userID<br> userName<br> Items (xml data type)</p></li> </ul> <p><strong>one - many relationship schema example</strong></p> <ul> <li>requires that a user to have at least one item list</li> <li><p>restricts users having the same unique item</p> <p><strong>User</strong><br> userID<br> userName ListItemID </p> <p><strong>List</strong><br> ListID<br> ListItemID<br> ItemID<br> Item</p></li> </ul> <p><strong>one - many relationship schema example</strong> </p> <ul> <li>restricts users having the same unique item </li> <li><p>does not require a user to have one or many items </p> <p><strong>User</strong><br> userID<br> userName </p> <p><strong>Item</strong><br> ItemID<br> UserID<br> Item</p></li> </ul> <p><strong>many - many relationship schema example</strong></p> <ul> <li>allows for a user to share the same item that other users have</li> <li>it can be very fast to retrieve information</li> <li>lots of redundant information. user and item information is stored multiple times</li> <li>if the details of a user changes, you will need to make updates to one or many records</li> <li><p>if the details of a item changes you will need to make updates to one or many records</p> <p><strong>UserItems</strong><br> UserItemID<br> userID<br> userName<br> ItemID<br> Item </p></li> </ul> <p><strong>many - many relationship schema example:</strong> </p> <ul> <li>this allows for a users to share the same unique item</li> <li>does not require a user to have a list of items</li> <li>it requires joining 3 tables to get information on the items the users have</li> <li>if user information changes only 1 record to update</li> <li>if the details of an item changes, only one record to update</li> <li><p>if user item list changes, the number of changes would be the number of updates/deletes/inserts needed</p> <p><strong>User</strong><br> userID userName </p> <p><strong>ItemList</strong><br> ItemListID<br> UserID<br> ItemID</p> <p><strong>Item</strong><br> ItemID<br> Item </p></li> </ul> <p><strong>many - many relationship schema example:</strong> </p> <ul> <li>this allows for a users to share the same unique item</li> <li>requires that a user have a list of items (one or more)</li> <li>it requires joining 3 tables to get information on the items the users have</li> <li>if user information changes only 1 record to update</li> <li>if the details of an item changes, only one record to update</li> <li><p>if user item list changes, the number of changes would be the number of updates/deletes/inserts needed</p> <p><strong>User</strong><br> userID userName ListItemID</p> <p><strong>List</strong><br> ListID ListItemID<br> UserID<br> ItemID</p> <p><strong>Item</strong><br> ItemID<br> Item </p></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload