I am creating a bookmark system where people can save their bookmarks. Each user has its own personal page with bookmarks. A bookmark should have atleast one tag and max five. Each user can create unlimited bookmarks / tags.
I created the following tables:
User
----
Id
Email
Bookmark
--------
Id
UserId
Title
Url
Tag
---
Id
UserId
Title
Description
TagBookmark
-----------
TagId
BookmarkId
Is this normalized? Or should I do this differently? Will this perform well with a million bookmarks?
I am also not sure if I should reuse already existing bookmarks / tags.
Example 1:
User1 and User2 both create the bookmark "http://google.com". Should I save this as one bookmark? Or save them for each user individually?
Example 2:
User1 and User2 both create the tag "search-engine". Should I save this as one tag? Or save them for each user individually?
If I do not reuse bookmarks and tags between users these tables may grow quite large.
Best Answer
Designing for maximum performance for an anticipated large user-base, I would suggest ensuring bookmarks and tags are not duplicated unnecessarily. Using my suggested schema below,
http://google.com/
would become a single bookmark row. Users who want a bookmark to Google would have a row inUserBookmarksTags
referencing the Bookmark row and the associated tag row.I would tend to abstract the UserID away from the Bookmarks table, and use a cross-reference table to determine which users have bookmarks, and what they've tagged each bookmark with.
Using SQL Server, I would do it like this:
If every entry in your system requires both a bookmark and a tag, you could use the following table for maximum performance:
The above version of the table
UsersBookmarksTags
allows you to quickly return a list of bookmarks for a specific user that matches any given tag, or all bookmarks for that user.Alternately, you could create the cross reference table that allows null entries for both Tags and Bookmarks, as in:
This allows rows that have a bookmark without a tag, and also allows tags to be associated to a user without a bookmark.
If there is a requirement to know all the tags a user has added, without those tags necessarily having an associated bookmark, I would consider adding another table for that purpose, as: