As users of SharePoint start using lists they start taking advantage of a very intuitive and highly configurable information management tool. Users who have been using Excel or Access in the past start seeing SharePoint as a kind of rapid application development platform for building structured data applications. However, this is where SharePoint’s list architecture breaks down because simply:
SharePoint is not a database!
Part of the problem is that business users designing lists are not familiar with basic database concepts - they just want to store their information. Part of the problem is false advertising by consultants and/or Microsoft that SharePoint is a miracle platform that can do anything - it does some things well but replacing your SQL server or even Access database isn't one of them. The other problem is there is no really good way for surfacing bi-directionally data from a database into SharePoint – the business data catalogue works only in read only mode and does not allow for updates back to the underlying data store.
Here are a list of some features that a database has that SharePoint does not support - the moment you need these features you will need a real database.
- Primary And foreign keys: one of the most basic concepts in a database is the enforcement of a unique identifier (either system generated or user supplied) that can identify a record and link across table in parent child relationships.
- Transactions: if two changes are required to go together and the second change fails you want the first change to be undone (rolled back).
- SQL language: complex queries can me written in code using a standard language. Queries can be saved and repurposed.
- Stored procedures: database programmers can write complex routines that can be called as reusable code blocks.
- Indexing and query optimization: used to improve performance, indexing allows the database designer to pre-index specific fields that are frequently used in queries to improve performance. In addition, most databases have optimization engines that based on what you are trying to fetch will optimize how the data is retrieved.
- Large data and binary fields: most modern databases allow you to store large binary files such as video files, large volumes of text, images, etc. in the database. SharePoint can be made to store large files as documents but you only get one per record and a generic binary object.
- Access outside of SharePoint: lists are accessible outside of SharePoint but only through XML or programming interfaces. Lists are not great data stores to be used for line of business applications written independently of SharePoint because the integration is relatively poor in comparison to running a SQL query on a database.
- Cascading deletes: if a parent record is deleted, its children should also be deleted. In most databases, this can be configured to happen automatically or else reject the delete of the parent until the children are deleted first. This eliminates the risk of orphan child records.
These features are considered basic in databases – they have been around for decades and even the most primitive databases such as Access or FileMaker Pro have most of these features.
Lists are great for flat, changing content especially when attached to unstructured documents. At least in the current version, lists are not substitutes for databases and shouldn’t be considered so. If you need a database, then you are better off building a proper database and using SharePoint as a front-end for surfacing that data through reports, dashboards, etc. or as a host for data entry components such as InfoPath forms or custom build web parts.
