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 -
  • 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.



  1. Hi Rajendra,

    Thanks for writing about SLAM! Just a couple of comments relating to your findings:

    1. You are correct that SLAM, out of the box, does not enforce referential integrity. The reason for this is that the event handlers are asynchronous so we couldn't handle the user interface aspect within the core. When we require referential integrity we typically try to alert the user on the front end (e.g. “are you sure you want to delete any associated cities?”). If you did want to delete all associated cities when you delete a country, SLAM provides a hook to do that pretty easily (by creating a SLAM profile).

    2. You talk about the “overhead of maintaining the slam config”. The reason we don’t just grab this from the metadata of the list is that you typically don’t need to slam all fields. You just want to slam the fields that are convenient to have in SQL. This also addresses (or mitigates anyway) your concern about the duplication of data.

    3. Regarding performance. It should be noted that the “synchronization” task is an initial set up task only. While it can take some time to synchronize a very large list, this has never been an issue for us given that the list is then kept synchronized in real time.

    Anyway, just wanted to say thank you again for helping us spread the word about slam.

  2. My current project requires ability to display choice column value via color or image associated with a choice

    But Sharepoint standard packaged misses that control

    I am looking for available solutions on market

    I came across

    Does anybody has experiece using it?

  3. share point management shell is useful in developing applications.

    Project Management Software


Search This Blog


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