Data Models in SharePoint 2010

Every custom SharePoint application is driven by data in one way or another. Because the SharePoint platform is geared toward managing the flow of information, it's hard to think of a solution development task that doesn't involve either displaying data, aggregating data, sorting data, filtering data, manipulating data, or creating data. SharePoint 2010 includes functionality that provides SharePoint developers with new ways to work with both internal and external data. For example, you can create relationships between SharePoint lists, in the same way that you might create a foreign key relationship between database tables. You can query across these relationships to support moderately complex data aggregation scenarios. You can use new measures designed to protect the performance of data-driven SharePoint solutions, such as throttling list queries to restrict the number of items returned by the query. You can interactively create external content types that define a set of stereotyped operations on external databases and services. A stereotyped operation is a data access method that conforms to a common and well-recognized signature, such as create, retrieve, update, and delete operations. You can create external lists, which effectively map a SharePoint list to an external content type. This allows users to interact with external data in the same way as they interact with standard SharePoint list data. Finally, you can use LINQ to SharePoint to query SharePoint list data using the Language Integrated Query (LINQ) syntax. This enables you to build sophisticated queries, including the use of joins.

This plethora of new features brings with it many new design options and tradeoffs for the SharePoint application developer. There are three core approaches to defining data models for SharePoint applications:

  • You can define a data model using SharePoint data structures such as lists and content types.
  • You can define a data model in an external data source, such as a relational database, and then create a set of external content types to expose the data to SharePoint through the Business Connectivity Services (BCS) component. Alternatively, you could create a Web service to wrap the data source and create your external content types from the Web service.
  • You can create a set of external content types to model and integrate an existing data source, such as a database, a Windows Communication Foundation (WCF) service, or a .NET type, to expose the data to SharePoint through the BCS.

This chapter is designed to guide you on key decision points and best practices for working with data in SharePoint 2010. The chapter includes the following sections and topics:

  • Understanding Data in SharePoint 2010. This section covers the key concepts behind the storage and management of data in SharePoint 2010. It describes the core building blocks for SharePoint data models, including lists, columns, and content types. It explains how list relationships work in SharePoint 2010, and it provides an insight into managing the impact of query throttling and indexing functionality.
  • External Data in SharePoint 2010. This section examines aspects of how you can use Business Connectivity Services to work with external data in SharePoint 2010. It describes options for modeling complex types and entity associations in a Business Data Connectivity (BDC) model, it explains how filtering and throttling works in the BDC runtime, and it maps common external data scenarios to different approaches to data modeling.
  • Data Access in SharePoint 2010. This section provides insights into the three main approaches to data access in SharePoint 2010—query classes, LINQ to SharePoint, and the BDC object model. It examines the benefits and limitations of each approach from the perspectives of usability, efficiency, and performance.
  • List Patterns. This section describes different design options to deal with common challenges faced with lists, including managing large lists and aggregating data across lists. It describes the benefits and consequences of each approach.

What's Not Covered in This Document?

Data in SharePoint 2010 encompasses a broad range of material, and there are some topics that are beyond the scope of this topic. The following subjects are not covered:

  • The Metadata Management Service, which manages the taxonomy and folksonomy data used in tagging.
  • Access Services and Excel Services, which allow users to publish Access databases and Excel workbooks on the SharePoint platform and use them through a Web browser. Access Services is a new feature in SharePoint 2010 that enables non-developers to assemble data-centric applications in Microsoft Access and then publish those applications to SharePoint. When published to SharePoint, the application is converted into a native SharePoint application based upon SharePoint lists. Access Services applications can be synchronized with the Access client for offline access to data and for report generation. For more information, see the Access Team Blog.
  • Business intelligence capabilities, which is a rich and important area, but it is not typically developer focused.
  • InfoPath-based solutions for custom user interfaces and data capture.