Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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 in UserBookmarksTags 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:

USE tempdb;
GO
CREATE TABLE Users
(
    UserID INT NOT NULL 
        CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , UserName varchar(255)
);
CREATE TABLE Bookmarks
(
    BookmarkID INT NOT NULL
        CONSTRAINT PK_Bookmarks
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , BookmarkName varchar(255)
    , BookmarkURL varchar(255)
);
CREATE TABLE Tags
(
    TagID INT NOT NULL
        CONSTRAINT PK_Tags
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , TagName varchar(255)
);

If every entry in your system requires both a bookmark and a tag, you could use the following table for maximum performance:

CREATE TABLE UsersBookmarksTags
(
    UserID INT NOT NULL
        CONSTRAINT FK_UsersBookmarksTags_UserID
        FOREIGN KEY REFERENCES Users(UserID)
    , TagID INT NOT NULL
        CONSTRAINT FK_UsersBookmarksTags_TagID
        FOREIGN KEY REFERENCES Tags(TagID)
    , BookmarkID INT NOT NULL
        CONSTRAINT FK_UsersBooksmarksTags_BookmarkID
        FOREIGN KEY REFERENCES Bookmarks(BookmarkID)
    , CONSTRAINT PK_UsersBookmarksTags
        PRIMARY KEY CLUSTERED 
        (UserID, TagID, BookmarkID)
);

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:

CREATE TABLE UsersBookmarksTags
(
    UsersTagsBookmarksID INT NOT NULL
        CONSTRAINT PK_UsersBookmarksTags
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , UserID INT NOT NULL
        CONSTRAINT FK_UserBookmarks_UserID
        FOREIGN KEY REFERENCES Users(UserID)
    , TagID INT NULL
        CONSTRAINT FK_UserBookmarks_TagID
        FOREIGN KEY REFERENCES Tags(TagID)
    , BookmarkID INT NULL
        CONSTRAINT FK_UserBooksmarks_BookmarkID
        FOREIGN KEY REFERENCES Bookmarks(BookmarkID)
);

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:

CREATE TABLE UserTags
(
    UserID INT NOT NULL
        CONSTRAINT FK_UserTags_UserID
        FOREIGN KEY REFERENCES Users(UserID)
    , TagID INT NOT NULL
        CONSTRAINT FK_UserTags_TagID
        FOREIGN KEY REFERENCES Tags(TagID)
    , CONSTRAINT PK_UserTags
        PRIMARY KEY CLUSTERED 
        (UserID, TagID)
);
share|improve this answer
    
Thank you for your answer. How would you query a tag for a specific user if this tag has no bookmarks? Because the only way to know this will be via the UserBookmarksTags table right? –  wbosland Dec 11 '13 at 20:15
    
Good question - I've edited my answer to show one way I would accomplish that requirement. –  Max Vernon Dec 11 '13 at 20:28
    
Could you describe why this schema is more preferable than mine? Perhaps performance? Thank you so far! –  wbosland Dec 11 '13 at 21:58
    
Bookmarks and tags in your schema cannot be shared among multiple users without duplicating data. I thought your requirement was to have a single row for each Bookmark, and a single row for each Tag, that could be joined to multiple users. I created a schema that provides the most de-duplicated data in a way that provides fast searching by User and Tag. –  Max Vernon Dec 11 '13 at 23:07
    
I have one short question. When a user deletes a bookmark or tag, the UserTags / UserBookmarkTags will be updated. Should I also delete bookmarks or tags in the Bookmarks / Tags table if nobody uses them? –  wbosland Dec 13 '13 at 9:46
show 1 more comment

This design looks perfect to me. I would keep bookmarks and tags distinct for each user, as you have it. This will save you from the complexity of which user has which tags for a shared bookmark, or which bookmarks for a shared tag. It's not like they'll be taking up a lot of space.

Your future self thanks you for not creating fields "Tag1", "Tag2", "Tag3", etc.!

share|improve this answer
add comment
User //Might be useful to have a seperate ID for the relationship between the user data
----
Id
UserId
Email


Bookmark
--------
Id
Title
Url


BookmarkUser //Lookup Table
------------
Id
UserId
Bookmark.Id


TagList //You don't need to associate users with tags
---
Id
Title


TagBookmark
-----------
Tags.TagId
Bookmarks.Id


BookmarkView //Suggestion. Aggregates data, will make queries easier
------------
User.UserId
Bookmark.Id
Tags.TagID
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.