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

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.

Answer 2

In addition to Maxim's answer, I would also advise that you take searching into consideration. OTB Search is really nice if this data is going to be something you will need to dig into.

Answer 3

I would not be too worried about going to a custom database for the data.

It does mean there is extra work to skin it with custom controls and introduce those controls to a layout page and/or custom webparts which the lists do for you.

If you have the BDC available, that would be the way to go, otherwise custom.

So in the end it is a trade off between the ease of integrating with sharepoint and having the data entry forms available vs coding all those items, but having full control of the data integrity.

Answer 4

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 5

Here is a slightly related question.