Using SharePoint Lists vs. Database Tables
An important decision in the design of the Training Management application was deciding whether to store information in lists or in a database. SharePoint lists consist of rows and columns that store data in a similar fashion to a traditional relational database management system such as SQL Server. However, a benefit of lists is that SharePoint includes Web Parts that provide simple methods for managing the data. If the data was stored in a database, it would require custom user interface components to access it and manipulate it. Also, specialized skills are required to design, implement, and maintain a custom database. Another advantage of using lists is that custom workflow and event handlers can easily be registered to them.
There are also advantages to storing data inside of a database. One is the availability of all the ACID (Atomic, Consistent, Isolated and Durable) properties of transactions. If your business logic requires transactions, storing data in a database is preferable to using lists. Also, SharePoint lists are meant to store simple data structures. If you require a complex data model with intricate relationships, a database is more appropriate. The Training Management application has three data storage requirements. It stores data related to training courses, registrations, and registration approval tasks. All the data is relatively simple and does not use transactions. The registration data also requires that there be a workflow. All these reasons make SharePoint lists the appropriate choice.
In general, reading and writing to a custom database provides an overall performance advantage. Although SharePoint has made significant strides in its performance, there is still a certain amount of overhead involved in processing lists. The SharePoint product team recommends limiting the number of items in a list to 2,000 for each list container. (A container is the root of the list and any folders in the list.) You may be able to manage larger lists by using views that are filtered so that no single view contains more than 2,000 items. Another way to circumvent this performance degradation is to write custom interfaces to manage and retrieve the data. (It is important to note that the 2,000 item threshold has more to do with HTML rendering performance than with the underlying performance of lists as a storage mechanism.) For more information about the performance of SharePoint lists, see the SharePoint product group's white paper, Scaling to Extremely Large Lists and Performance Access Methods.
The following table summarizes the benefits of using databases and SharePoint lists.
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 |
For examples of using SharePoint lists, see The Training Course List, Adding an Item to the Registration List, and Updating a Registration List Item.