One of the highlights of the SharePoint 2009 Conference was the new list relationships improvements and many of us excited to build the future systems based on the SharePoint List’s many to many relationship improvements. As many of us aware, there are two kinds of list relationships – parent/child or master/detail relationship (1-n relationship in database terms) and many to many relationships. In SharePoint 2007, there was a lookup column where you can define the columns based on another list. It enabled the linking one list to another and end-users can easily build the parent-child related lists. If you would like to bring another column from the parent list to the child list, it wasn’t possible through out of the box features.
In SharePoint 2010, Microsoft improved list capabilities which would not only bring the additional columns from the another list based on the lookup columns but also enforce the relationships. To create the related lists, you have to use the lookup columns to form the relationship between lists.
- Projected Fields or Secondary Columns – Users should be able to reference the additional fields from the parent list to the child list to provide the read-only view of the data from the another list.
- Relationship Behavior and Data Integrity – There are three possibilities for the list relationship integrity and deleting parent item would have different behavior based on different options. Please remember that all three options would allow deleting child list items without affecting parent list items.
- Restricted Delete with enforcing relationship behavior – This option would prevent the end user deleting the parent item if it’s used in the child lists by enforcing the data integrity. Lookup column enforcing the relationship would also create the index on the column. If user tries to delete the parent item referenced in the child lists, they will get the message – “This item cannot be deleted because an item in the “Child” list is related to an item in the “Parent” list.”. This option prevents creating the orphaned records.
- Restricted Delete without enforcing relationship behavior – This option would allow deleting the parent item without enforcing the data integrity but clears the references of the parent item in the child item. Child list item will contain “Blank” value for the each deleted parent referenced column. This option will create the orphan records in the child list and you need mechanism like SharePoint workflows to clean up the orphan records.
- Cascade Delete (You have to enforce the relationship behavior) – This would allow deleting not only parent item but all the child items referencing the parent item. Lookup column enforcing the relationship would also create the index on the column.
- Optionally you can make the fields required and enforce the uniqueness on the column. Please remember that Uniqueness is not the case-sensitive and will create the index on the column.
- You can also decide to allow single valued selection or multi-valued selection on the look up columns. Single-Valued Lookup columns are supported as a unique column and Multi-valued lookup columns are not supported as a unique column.
One thing potential SharePoint implementers needs to keep in mind that building SharePoint List relationships with lookup columns isn’t same as the true SQL-like many to many relationships. SharePoint doesn’t enforce having an intermediate list to hold the many too many relationship data. As a SharePoint Architect, you may able to skip the intermediate SharePoint list to build the one-to-many relationship or design the intermediate SharePoint list like intermediate SQL table and enforce the relationships to build the many-to-many relationships. Things will get tricky when you try to crate the data entry pages or forms to maintain these relationships (this may be a great reason to build the custom ASP.NET applications instead of customizing the SharePoint). You may able to use the SharePoint Designer Linked Data Sources or Related Item View (new feature to create the master-detail views) to create the mashups or programmatically write a code using Linq-to-SharePoint to implement the renormalized views to retrieve the data by joining the multiple lists.
There are handfuls of blog entries discussing newly improved SharePoint 2010 list capabilities and near parity with SQL Server based database relationships.
Hope this blog would clarify many details around the SharePoint 2010 List relationships.