CODEWITHSUNDEEP
mysqldatabase-design
user656925
user656925

Reputation:

How do I store bookmarks for multiple users in a mySQL table(s)?

Suppose I have users u1,u1,u3...

and bookmarks b1,b2,b3....

How do I store the bookmarks most effectively.

Each user has his own set of bookmarks that load on to his page. Then, if the user wants to know who else has the same bookmarks, he can select that bookmark and see a list of users who also have it.

This is just a question about general design. If my understanding is correct so far this is a many to many relationship.

Upvotes: 0

Views: 1196

Answers (1)

Flatlin3
Flatlin3

Reputation: 1659

the common approach would be to use a third table (many to many relation) you may also store a timestamp in this table to determine when the bookmark was added by the user or a user specific comment for his bookmark.

Another great thing about this approach is that the bookmarks table doesn't store any user specific data or has duplicate entries (4 times the same bookmark cause 4 users added it)

+-------------------+
| bookmarks_users   |
|-------------------|
| id (int, primary) |
| user_id (int)     |
| bookmark_id (int) |
+-------------------+

Select all bookmarks of one user (just a basic query - you maybe should join the needed data ;) )

SELECT bookmark_id
FROM bookmarks_users
WHERE user_id = 123

select all users having a specific bookmark

SELECT user_id
FROM bookmarks_users
WHERE bookmark_id = 123

show users having the at least one common bookmark as the specified user (this query is very bad (performance wise) but it serves well as an example)

SELECT DISTINCT user_id
FROM bookmarks_users
WHERE bookmark_id IN(
    SELECT bookmark_id
    FROM bookmarks_users
    WHERE user_id = 123
)
AND user_id != 123

hope this helps - tim

Upvotes: 2

Related Questions