A Common dilemma – SharePoint lists vs DB Tables


One of the most common design mistake I see people repeating over and over again is the usage of SharePoint lists as a database table. SharePoint is not an RDBMS.. period. If you have any application in which you planning to make use of SharePoint list to store transactional data, please.. think again, else you would be soon into a plethora of performance issues.

Take a look at this table from the MSDN link http://msdn.microsoft.com/en-us/library/ff647105.aspx
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
This table was created when SharePoint 2007 was the latest version and SharePoint 2010 brought in more features to lists – cascading deletion , the ability to pull link two fields better when doing a lookup, more capacity for list views etc. Although these features were a blessing for people using lists for what they were actually meant for, it added more confusion to people starting off with SharePoint.
For a person taking a look at SharePoint lists, it would appear to be a database table with a end user UI inbuilt . It would seem like an ideal place to store information; developing a UI for basic CRUD operations making use of database tables and ASP.Net code would take atleast a week of effort taking into account UI formatting, unit testing etc .etc.
In situations where we are debating SharePoint lists vs Database tables these are the aspects I consider assuming functionality wise everything is satisfied
  • Number of items you are planning to store

Going by the documentation defined in the SharePoint boundaries and limits the limit is 30,000,000 items per list. Pretty big and for most purposes you are looking for, this may be way more than enough. However there is another number 5000 which is the list view threshold which is the maximum number of list items that can be processed at one point in time. Sure this threshold can be configured, but it is not recommended and performance would be impacted if you try to query more than this. In a normal scenario you may think that this is not necessary, however once you have data in, the next thing that people are going to ask you for is analytics on the data in which would have to work with larger chunks . (Just a note the 30 million limit comes with a clause – ‘This value may vary depending on the number of columns in the list and the usage of the list.’)

  • Usage of the list

Ok, so all the limits are acceptable, and you needs do not call for a huge data volume, the next thing to consider is how you are going to use the list. Will people just use the out of box list views with some UI, XSL customization or are you planning to use a custom web part or component to read the data and display? The out of the box views are pretty much optimized, but if you planning to carry out queries, do consider the fact that SharePoint is not optimized for read. The only option available for optimization is the possibility of indexing two columns in a list (this indexing is not possible in all column types).  SQL Servers has a range of query optimization possibilities; making use of multiple database level indexes, query hints, compiled stored procedures etc.

Other aspects are doing aggregations, combinations etc. from lists are not something’s very easily done, so you need to figure out all the operations you would be using the list for in advance rather than just saying, the data is there so we’ll be able to get it out, else you may end up having to do a loop through the list items to get the record you want.
  • Security
 This is one of the most overlooked aspects I have seen so far. Data in a DB stays there unless someone writes code to expose it out and that code can have all the security checks to ensure that the queries to the DB just returns items relevant to him.
SharePoint is meant for collaboration, everything is open by default. If users have read access to a site, they have read access to all the lists and libraries as well. Most custom applications would require users from having access only to certain sets of information. There are options in SharePoint to have views that filter the rows so that when a person views the list, he only sees information assigned to him etc. however what needs to be considered is that SharePoint is a web application and all items have URLs associated to them. Just because by default the list view is not shown to a user or a custom page is used for user interaction does not mean user cannot type a URL and get to any list item in the system to which his permissions has not been explicitly removed. Other access points to information are search results, Client object model, web services etc. All of these can be used to access data which a user is not meant to view.
One workaround for this is to break permissions at an item level. This would ensure that only users who have been given permissions would be able to view the list items and would have been an ok solution if it was not for the limitations it too poses documented here http://www.microsoft.com/en-in/download/details.aspx?id=9030  if you have a large number of users.
Hope this helps in your next SharePoint solution!
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: