SharePoint: should I use lists or a database?

Question

I am in a process of designing a custom SharePoint application. On a previous project, all the data was kept in SharePoint lists and that's the way I've been trying now. But, I'm getting to the point where the data model grows and I feel a need to normalize it and split one logical entity into several physical lists. I'm wondering if I should switch from the SP lists to a classic database. On one hand, I am happy with the SharePoint out-of-box New Item, Edit Item, All Items forms; on the other hand I'm worried that the performance will suffer once I have to query joined data (if it stays in SPLists).
If you have any insight or experience with this problem, please share. Thanks.

Answer 1

It depends on your requirements, but from my experience here are the cases when you should use database instead of lists:

1) When you have a many-to-many relationship in your database model

2) When you have two or more entities linked together (e.g. Customer > Invoice > Invoice Product).

SharePoint is great but in the above scenarios you will have problems with SharePoint UI limitations.

3) If you plan to have any custom reports or charts you should stick to your own database.

When you are using database entities the best approach is to develop your own web parts since BDC is expensive and very limited for most cases. You can also check 3rd party web parts (e.g. Bamboo Web Parts)

---

Here are the reasons to use SharePoint lists over database:

• Permissions
• Ease of usage for the end user
• Edit in datasheet / Excel / Access
• Workflows
• Search

Answer 2

If you have complex queries, I suggest you put them into a separate database. Lists are good when the data model doesn't grow that often.

Extending the amount of fields inside the columns of a list includes updating the ContentTypes directly with an STSADM that you will have to code. However, querying the data directly from the database (with some cache of course) will lead to a faster development without having to update all ContentTypes linked to every lists associated with it.

Of course, if you activate the Caching, the data queryied from the database will be cached at the page output level.