Friday, January 21, 2011

SharePoint 2010 Social Networking: Part 5a - Bookmarking and Tagging Database

In this post, I will dive into the details of the social bookmarking and tagging features of SharePoint 2010.  As I said in a previous post, my aim with this series on SharePoint 2010 Social Computing is to bring a new level of enlightenment to how the features have been designed to work, their intended use, and benefits.
I am actually going to break this discussion up into four separate posts because of the depth of the subject area and the investment Microsoft has made in the features.  The work that Microsoft has done to enable social bookmarking and tagging within SharePoint 2010 is both broad and deep, and a single blog post on the subject would be too long.
Here is how I am going to break up Part 5 of my series into four blog posts:
Part 5a - Intro and Central Database of Bookmarks and Tags For All Users < You are here
Part 5b - Bookmarking and Tagging User Experience
Part 5c - Browsing Bookmarks and Tags
Part 5d - Searching Bookmarks and Tags
(Note: This post begins the fifth part in this series on SharePoint 2010 Social Networking. For the first post and a table of contents, go here.)
To get started on explaining social bookmarking and tagging in SharePoint 2010, I am going to take an approach that some people may not consider very fair to SharePoint, but I believe that you will see by the end of the article that it is a very good way to analyze and understand the features available.  My approach: compare social bookmarking and tagging in SharePoint 2010 to the features of Delicious.
sharepoint-deliciousDelicious could be considered the grandfather of all social computing applications.  If not the grandfather, then certainly one of the top two or three from the early days of social computing on the Internet - definitely long before Facebook and Twitter.
At its core, Delicious is a database.  It is a database of URL bookmarks with users and tags associated with them.  It allows users to bookmark interesting content using their favorite web browser(s), assign free form subject-related tags to the bookmark(s), and then save the information to the Delicious database.  The fact that the information is stored in the Delicious centralized database opens up the door for surfacing and sharing of bookmarks and tags among the community of users of the service (hence, the label of “social bookmarking” to describe the service).
Practically speaking, many users have found over the years that a social bookmarking application can help them uncover excellent content on the web that for some reason doesn’t surface easily in the search engines.  I know a lot of people who consider a Google search and a Delicious search as their one-two punch when they are looking for a particular type of hard-to-find content.  They frequently say that if they can’t find any relevant information about what they are looking for by using Google, that often they will find something through Delicious.
Microsoft has communicated over the last year that providing social bookmarking and tagging features was one of the primary goals of SharePoint 2010 Social Computing.  The thought is that if all of an organization’s users contribute to the centralized database of bookmarks and tags, individuals and the organization as a whole will reap significant benefits from this type of sharing of organizational knowledge. (Remember that I said in my first post that social computing in SharePoint 2010 is really more about “knowledge networking” than it is about “social networking”).
So, let’s see how SharePoint 2010 stacks up to the feature-set provided by social bookmarking gold-standard, Delicious. Through the process of doing this, I believe I can do a good job of explaining the details of the SharePoint 2010 approach.

Feature: Centralized Database of Bookmarks and Tags For All Users

(Note: this first bookmarking and tagging feature discussion is a little on the technical side, but is very important to the end-user features that I tackle after this one.)


No question about it, Delicious has this.  Without it, the service wouldn’t work, and we know it has worked quite well and has been quite popular for many years.  I can’t describe the specifics of any of the Delicious database architecture, though, since it is proprietary and not shared by Delicious with the public.  We will just trust that it is solid since it has stood the test of time.

SharePoint Server 2010:

Using SQL Server Management Studio, it is relatively easy to look under the hood of SharePoint Server 2010 and get some architectural details about the databases and tables that underpin bookmarking and tagging,
The dbo.SocialTags table:
Looks like this table is storing a record for each instance of a user tagging/bookmarking a page in SharePoint 2010.  It has a date and time stamp (LastModifiedTime) a tag label text field (InputTermLabel) and a page title field (should be the title of the page bookmarked, but appears as NULL for some reason in the few sample records shown above).
As expected, it also has a foreign key (UrlID) that could be used in joins to a master URL table (dbo.Urls).  This one-to-many data structure should make it easy to retrieve all of the bookmark/tag records for a given URL.
(Important note: By explaining what I see in the SharePoint databases, I am not advocating that anyone directly read or write to the SharePoint tables.  The only supported way of accessing SharePoint data is through the object model and web services published by Microsoft.)
Also as expected, it has a foreign key (User_RecordID) that could be used in joins to a master User table (dbo.UserProfile_Full).  This one-to-many data structure should make it easy to retrieve all of the bookmark/tag records for a given User.
Finally, it has a foreign key (TermID) that could be used in joins to a master Tag Label table (the dbo.ECMTermLabel table in the Managed Metadata Service database).  This one-to-many data structure should make it easy to retrieve all of the bookmark/tag records for a given Tag Label.
So, a quick and dirty* Entity Relationship diagram might look something like this:
* I say “quick and dirty” because this is just my interpretation of the relationships based on what I can surmise from looking at the tables and columns in SQL Server.  To my knowledge, Microsoft does not publish official documentation that describes the relationships or where and how they are used.

Comparison: Bookmarks and Tags Database Design

It looks like SharePoint has well-designed, capable data structures for accomplishing the primary goals of social bookmarking and tagging - storing and easily retrieving shared bookmarks and tags.  We don’t know exactly what the Delicious structures look like, but it is probably a safe bet that it is similar - the data design of this type of application is not exactly “rocket science”.