SharePoint Use Cases

  • Home
  • About
  • Our Tools
  • Contact me
SharePoint

Storing metadata in SharePoint Lists vs. SQL Database

Toni Frankola - November 3, 2008

In every major project we are running, there is always a request to store some additional metadata to SharePoint. Usually this data describes :

  • processes running on SharePoint platform
  • document attributes
  • users

…or customer just wants to have the same, familiar web interface or a single web application for the end user.

Few days ago I stumbled upon a discussion on this topic at StackOverflow (learn more about StackOverflow). My response is currently marked as the correct one :).

In this post I will try to give some additional details:

Using SharePoint lists to store meta-data

Here are the key benefits why you should use SharePoint custom lists to store data. These features are hard to build from scratch but are available within all SharePoint editions:

  • Edit in datasheet / Excel / Access
    This is probably the most important benefit of using SharePoint as data store for the metadata. Users just love to edit their data from web-based datasheets. It is easy to use and updates can be done in seconds instead of minutes.
  • Check-in & Check-out
  • Versioning
  • Permissions
  • Ease of use for end users
  • Workflows
  • Search

Using SQL database to store metadata

In some scenarios SharePoint custom lists will not be adequate and you will need to use SQL Database to store data. In a scenario like this you will need to create a custom database to store data. Here are some cases when you should do it:

  • You have a many-to-many relationship in your database model
    These kind of relationships are problematic because you will need to customize UI in order to allow user to easily join two entities. The built-in interface works OK if there are only a few values on each side, but for couple of hundreds of items it won’t be too useful.
  • Two or more entities are linked together (Customer > Invoices > Invoice Products)
  • SharePoint lists are great when an entity is linked to another entity (One-to-Many relationship). But if you want to link two or more together (in series) this might be tricky and is not supported out-of-box.

For both scenarios mentioned above you will probably want to display data in master > detail format and that is not easy to achieve.

  • Reporting
    If you plan to create reports or charts about metadata, stick to plain SQL database. It is much easier to create Views over your own plain data then the SharePoint data (this is also not supported, you should always access SharePoint data via API).

You should definitely consider Business Data Catalog as a solution to integrate your custom data with SharePoint. It is easy to use and no programming required. Bear in mind that it is only availabile with SharePoint Enterprise CAL. If you are using SharePoint Standard CAL or Windows SharePoint Services 3.0, then BDC is not availabile.

For such scenarios you can either develop your own solution or use a 3rd party solution like Bamboo MashPoint or some other solution by Bamboo, LightningTools and other vendors. All these solutions have pros & cons, so before embarking a project do some prototyping to avoid problems.

Tags | best practices, metadata
 0
Share Now

Toni Frankola

Entrepreneur, IT consultant, speaker, blogger, and geek. Co-founder of syskit.com.

You Might Also Like

SharePoint

Team-Based Development in Microsoft Office SharePoint Server 2007

December 16, 2008
SharePoint

Team-Based Development in Microsoft Office SharePoint Server 2007 – Part 2 – Building a development lab

December 21, 2008
SharePoint, SharePoint 2010

SharePoint installation and maintenance slide decks from Windays 2012

April 27, 2012
M365 Security Best Practices Office 365

50 Best Practices for Securing Microsoft 365

January 12, 2021
Previous Post SharePoint Usage Reports explained
Next Post GMail now supports voice and video chat

Archives

  • December 2021 (1)
  • June 2021 (1)
  • January 2021 (1)
  • July 2019 (1)
  • June 2019 (2)
  • March 2019 (1)
  • February 2019 (3)
  • October 2018 (2)
  • August 2018 (2)
  • June 2018 (1)
  • April 2018 (1)
  • February 2018 (1)
  • January 2018 (1)
  • June 2017 (1)
  • April 2017 (1)
  • February 2017 (1)
  • November 2016 (2)
  • August 2016 (1)
  • July 2016 (2)
  • March 2016 (1)
  • February 2016 (2)
  • January 2016 (1)
  • November 2015 (2)
  • December 2014 (1)
  • November 2014 (2)
  • July 2014 (2)
  • June 2014 (2)
  • May 2014 (1)
  • April 2014 (2)
  • March 2014 (1)
  • February 2014 (4)
  • January 2014 (2)
  • December 2013 (1)
  • September 2013 (1)
  • July 2013 (1)
  • June 2013 (1)
  • May 2013 (1)
  • April 2013 (3)
  • February 2013 (1)
  • December 2012 (1)
  • November 2012 (1)
  • September 2012 (1)
  • August 2012 (1)
  • July 2012 (2)
  • June 2012 (4)
  • April 2012 (1)
  • February 2012 (1)
  • January 2012 (1)
  • November 2011 (2)
  • October 2011 (2)
  • September 2011 (2)
  • July 2011 (3)
  • May 2011 (2)
  • April 2011 (1)
  • March 2011 (4)
  • February 2011 (3)
  • January 2011 (4)
  • December 2010 (4)
  • November 2010 (3)
  • October 2010 (3)
  • September 2010 (4)
  • August 2010 (1)
  • July 2010 (4)
  • May 2010 (2)
  • April 2010 (2)
  • March 2010 (3)
  • January 2010 (2)
  • December 2009 (3)
  • November 2009 (5)
  • October 2009 (2)
  • August 2009 (7)
  • July 2009 (3)
  • June 2009 (4)
  • May 2009 (3)
  • April 2009 (5)
  • March 2009 (8)
  • February 2009 (5)
  • January 2009 (8)
  • December 2008 (9)
  • November 2008 (8)
  • October 2008 (7)
  • September 2008 (4)
  • August 2008 (6)
  • July 2008 (5)
  • June 2008 (3)
  • May 2008 (4)
  • April 2008 (6)
  • March 2008 (4)
  • February 2008 (3)
  • January 2008 (6)
  • December 2007 (1)
  • November 2007 (3)
  • October 2007 (3)
  • September 2007 (2)
  • August 2007 (1)
  • July 2007 (12)
  • June 2007 (2)
  • May 2007 (3)

Connect Us

All postings on this blog are provided “AS IS” with no warranties, and confer no rights. All entries in this blog are my opinion and don’t necessarily reflect the opinion of my employer.

  • Home
  • About
  • Our Tools
  • Contact me

Copyright (c) Toni Frankola 2008. - 2019.