StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
14666035
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
5
CommunityOwnedDate
CreationDate
2013-02-02T20:13:51.780
FavoriteCount
0
LastActivityDate
2013-02-02T21:00:48.330
LastEditDate
2013-02-02T21:00:48.330
LastEditorUserId
306480
OwnerUserId
306480
ParentId
14665943
PostTypeId
2
Score
10
ViewCount
0
LastEditorDisplayName
text
Body
You should use 2 tables to store this data. <pre><code>users: username | password | email ------------------------- items: username | item </code></pre> You can then insert an unlimited number of items for a given username in the second table (each item is a new record). You can then select all the items for one user like this: <pre><code>SELECT item FROM items WHERE username = ? </code></pre> You can also combine the 2 tables in a number of ways. <pre><code>/* this one selects all users who have a specific item */ SELECT u.* FROM users u JOIN items i ON u.username = i.username WHERE i.item = ? </code></pre> How do you wish to use the data? You should also start to use ID values (numbers) so that the you don't have to waste space repeating text based content such as the username. <hr> <blockquote> I think this might be a really good way, however can you show me what you mean by each item is a new record? I'm still a bit confused. </blockquote> You could insert the values like this: <pre><code>insert into items values ('jonathan', 'hat'); insert into items values ('jonathan', 'drink'); insert into items values ('jonathan', 'mouse'); insert into items values ('user1981730', 'hat'); insert into items values ('user1981730', 'mouse'); </code></pre> Which would result in this: <img src="https://i.imgur.com/G9cvHyS.png" /> Then you could query for all users who have drink as an item: <pre><code>SELECT u.* FROM users u JOIN items i ON u.username = i.username WHERE i.item = 'drink' </code></pre> <img src="https://i.imgur.com/GEtQZEZ.png" /> Then to fetch a list of all items obtained by user1981730: <pre><code>SELECT item FROM items WHERE username = 'user1981730' </code></pre> <img src="https://i.imgur.com/iVunMbh.png" /> Once you are happy with this we can talk about adding ID columns. <hr> <blockquote> what do you mean by ID columns? </blockquote> Notice that in the above example the items table has a lot of duplicate information. The username jonathan is repeated 3 times, the item hat is repeated twice. This would be much worse with a real database. We can save space and generally increase performance by using an integer ID to represent each unique value. Each user would have an ID and so would each unique item. Let's start with just the users. We must add the new column to the users table. <img src="https://i.imgur.com/8My0zAZ.png" /> We make it a PRIMARY KEY which will ensure that all values are unique so that no 2 users can have the same ID. We can also use the AUTO_INCREMENT to have the value automatically increase with each new record. <img src="https://i.imgur.com/5Aa3hgI.png" /> <img src="https://i.imgur.com/ECe2YIu.png" /> We would then change our items table so that it made use of the new column instead of username. <img src="https://i.imgur.com/3Di4F2h.png" /> Notice how we still have the same information that we did before. User with ID number 1 has a drink, hat and a mouse and the user with ID number 2 has the hat and mouse. We can still do the same queries as before (with some small changes). <pre><code>/* this selects all the items for a known user_id */ SELECT item FROM items WHERE user_id = ? /* this selects all the items for a user when we only know the username */ SELECT i.item FROM items i JOIN users u ON u.user_id = i.item_id WHERE u.username = ? /* this one selects all users who have a specific item */ SELECT u.* FROM users u JOIN items i ON u.user_id = i.user_id WHERE i.item = ? </code></pre> So far we have only modified the tables to avoid duplicating the username. If we wanted to also avoid duplicating item names we can add an additional table. This starts to get tricky so I won't go into too much detail (for now) but the table structure would be as follows: <pre><code>users: username | password | email ------------------------------- items: item_id | name ------------------------------- users_items: user_id | item_id </code></pre> The users table is as before. The items table has a new role and it stores all the unique items. The users_items table now links the other 2 tables. <hr> I will end the SO answer here. If you have any further question you can email me with the address listed above (the one used in the example table). 
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POHow to add multiple items in a MYSQL column
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USdiolemo
UserOwnerUserId
1. USdiolemo
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POHow to add multiple items in a MYSQL column
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.