Through this blog, it is my effort to share experiences with the community and make the journey enjoyable.

Sunday, May 03, 2009

SharePoint List Association Manager

While starting on design of SharePoint Custom applications, one of the dilemma which comes - Whether to use SharePoint lists as DataStore or Use RDBMS for storing master and transaction data.

Advantages of SharePoint Lists -

  • Out of box User Interface, reduces development effort
  • Available UI Column types of text, multi line, drop down, radio button, date time etc.
  • Allow lookup columns to define relationships between lists.
  • Sorting and Paging
  • Different Views based on user needs
  • Multiple Records Data Entry
  • Email Alerts
  • Integration with Excel, Access etc.
  • Provides RSS Feeds

Implementing Database Entities as SharePoint Lists, allow users to perform CRUD operations and out-of-box UI with above features.

Limitation of SharePoint Lists for maintaining Database Entities -

  • As SharePoint manages the storage and retrieval of data, Application developers has no control over it, to optimize or tweak the operation.
  • Performance Issues of SharePoint lists : It is well known about SharePoint List performance issue. Refer - http://sharepoint.microsoft.com/blogs/GetThePoint/Lists/Posts/Post.aspx?ID=162
  • Basic Database Features missing in SharePoint list - Unique Constraints, Foreign Key relationships between lists, Database Constraints etc.

SharePoint List Association Manager ( SLAM ) overcomes one of the limitation of defining relationships on SharePoint lists. In nutshell, it creates the Database Tables of Lists and Relationships in External Database. Uses SharePoint eventhandlers to update external database list and relationship tables. Association of lists is based on SLAM xml configuration files. External Database provides independent datastore for reporting, and provides performance gain.

Working with SLAM, there are few things, I found out ( based on my current understanding) -

Explaining by taking example of two SharePoint Lists 'Country' and 'City'. 'Country' has a single text column 'CountryCode'. 'City' has two columns - Text column 'CityName' and lookup column from 'Country' list.

  • No constraints on lists at SharePoint lists. It allows deletion of data from 'Country' list, even if we have relationship data in 'City' list.
  • Allows deletion of parent SharePoint list 'Country', even after relationship defined.
  • Duplicates the data between SharePoint list and External Database Tables.
  • Overhead of maintaining SLAM config, as we have already defined metadata for lists in SharePoint.
  • Performance Issues - Takes approx. 30 minutes to sync SharePoint lists having 20000 records. Concerns on implementations with Enterprise applications with large amount of data.

SLAM doesn't provide Database features of primary keys, constraints, triggers etc.

In my view, SharePoint lists should be mistaken as replacement for Relational Database Management System. For small amount of data and keeping SharePoint lists constraints, might be useful for smaller chunk of data.

Share:

Search This Blog

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway. All posts are provided "AS IS" with no warranties, and confers no rights. In addition, my thoughts and opinions often change, and as a weblog is intended to provide a semi-permanent point in time snapshot you should not consider out of date posts to reflect my current thoughts and opinions.

Popular Posts

Total Pageviews

Blogger Tutorials

Blogger Templates

Sample Text