Thursday, January 29, 2009

When to use a SharePoint List?

It has been over a year since I have wanted to write this blog. It is a little late now but whatever; the topic still seems to come up. This blog struck a chord with me all of the sudden because I got my hands on the Microsoft Patterns and Practices SharePoint Guidance for November 2008. There are some good things in there, but much of it common sense sort of stuff.

This is what is in the SharePoint Guidance for November 2008 article when it comes to make a decision about using SharePoint Lists vs. a Database.

Benefits

Database

SharePoint list

Handles complex data relationships

Yes

No

Handles large numbers of items

Yes

No

Handles transactions

Yes

No

Is easy to use

No

Yes

Accommodates workflows

No

Yes

Includes a standard interface

No

Yes

Can easily add binary data

No

Yes

For the most part, they get it right however I think there are some things that are worth adding. My two cents is if I have any sort of custom application with custom code I usually start moving away from using SharePoint Lists and move towards SQL Server.

Here are two small cases studies. The first I had a client that needed simple application where a custom view of data was needed, the data was flat and non-relational, no reporting required, and we needed ability to approve and deny visibility of data. The solution entailed creating a Feature which deployed a content type, we created a custom list for that content type, we created some event receivers, we used the out of the box content approval for SharePoint lists and we created a web part for the custom view of data. By using a SharePoint list, we were able to get some free functionality like approving the visibility of data. In this case using a SharePoint List made tons of sense and worked very well.

The second case study did not work so well. We had to create a custom task management application. This task management application had several entities and some basic reporting requirements which needed to be exported from SharePoint:

  • We used SharePoint content types to model each data entity. Then we put all of the content types and data in the same list (effectively creating a three-dimensional table), pretty cool in concept.
  • We used event handlers to implement state management and relational rules between the data entities.
  • We wrote a custom data provider to get data out of SharePoint Lists into Reporting Services.
  • We over-overrode the list edit controls to we could implement more custom functionality.
  • We wrote several custom web parts that used CAML to query items out of the list since we were going go over the 2,000 item threshold. We had over 50,000 items in the list with zero performance problems.

In the end, we met every requirement and implemented on time and budget. The solution did not last long because it was a task management system where functionality needed to change in a rapid fashion and the reporting requirements grew and grew. The business users wanted to do all the reporting in Business Objects which was a tool they were very familiar with. Business Objects can use SQL but not SharePoint Lists so the solution did not scale. Looking back at the architecture of the solution I was disappointed in it because we had considered using SQL Server instead.

What we learned was highly relational data is not easily enforced using event handlers and if there are any sort of reporting requirements SharePoint Lists are not the way to go.

Plus I came out of this with some golden rules:

  • If I am writing custom web parts to retrieve and set data (unless I am getting some free SharePoint functionality by using a list item) I am going to go with SQL.
  • If I have relational data (other than simple lookups), going to SQL.
  • If I have reporting requirements, going to SQL.

Now based on several client experiences and emails I get CC'ed on for other SharePoint projects I felt it was necessary to go ahead and get this down. What I have also seen is lots of functional business users using SharePoint Lists as a way to replace MS Access or they think of it as web enabled Excel Spreadsheets (not to be confused with Excel Services). I have even seen designs from others where they start dropping XML documents into lists and start using that as a way to store enterprise data. I have even heard a request from someone on how to create an entity relationship diagram for list data they have. This is probably just a sample of bad uses of SharePoint Lists...

In the end my rule is simple, use SharePoint Lists for day-to-day collaboration sites. The point you start needing to build an application with custom code with web parts and business reporting stop right there; SharePoint Lists should probably not be used. Reporting seems to always be the deal breaker for a SharePoint List. Usually when building reports there is requirement for relational data. Do not fall into trap where you believe that if you use a SharePoint List you get free read, insert, update and delete screens. Your application will not scale over the long run.

There are several 3rd party companies that have sprung up created suites of tools for get around this. For instance there is Bamboo Solutions, KWiz, Coras Works, etc.

2 comments:

Halling said...

I agree in your view of how and when to utilize a Sharepoint List!

I have found the same problems you describe but have failed to persuade my superiors which means Im now stuck with a complex relationship model implemented in Sharepoint Lists.

Word of advice: Dont go there...

Jason Apergis said...

Halling,

This guidance is from SharePoint 2007 days which has not really changed for SharePoint 2010. SharePoint 2010 supports referential integrity however it is still not very complex. Another good thing with SharePoint 2010 is when you a database, you can use Business Connectivity Services to quickly perform insert, update, delete or stored procedure transactions.


Lists are still great to use but just need to use them when you do not have complex data relationships.

Jason