How to decide whether to store data in a single list or spread it across multiple lists

Question

We are using SharePoint 2013 On premises version. We have a requirement where I need to store data in SharePoint.

On the home page, users need to take actions on the data and the data that is displayed should not have any latency. Initially, I was planning to use different lists to store the data. But, if I use that approach, I need to pull the data using search as I can’t iterate through 25 different lists to display data on the home page. The problem here is that the data won’t be latest. If a user takes any action on the data, the next user would not see the updated data unless it is crawled and indexed by SharePoint search.

My questions are:

What is the implication if I store all the data in a single list categorized into different content types? I would have 200 items per day. I looked at the column limit and I guess that shouldn’t be an issue as I would have around 100 columns spread into different content types. I would be able to reuse many columns in different content types.

Obviously, list threshold limit is a major concern when there are more than 5000 items in the list. I guess we would hit that limit in a month. But, we are also planning to archive the data 6 months after it is created. So, at a certain point of time, I may not have more than 50000 items in the list. Is this still a very big number?

I also need to use this data to generate different reports. However, I can use search OR SSRS there as that data doesn’t have to be latest.

Any advice on best practices are welcome. Thank you in advance.

Answer 1

We have to understand that threshold limit of 5000 is to retrieve data in single query. Below are some points you can consider or take care while designing your information architecture.

1. The data you would be displaying on home page, you need to identify, Is there an requirement or may be in future that a single query to this list will return you more than 5000 items. Obviously you would have some filters on some columns.
2. Create indexes on columns well before threshold value is passed, you need to identify columns which will be used to filter data in your CAML query.
3. 50000 items in a list is not a big number, you can keep this in single list.
4. Reports which you are talking about, based on requirement make sure that any report will not cross 5000 items limits after filters passed in CAML query.
5. If you are passing multiple filters, you have to make sure first column filter should return below 5000 items.
6. As you mentioned that you would be archiving data after 6 months, this would be a good start.
7. You can also design application so that you can provide multiple views options to user, 1. to view current/latest data and 2. To view archive data. As both this pages can query to different list.
8. Try to add as many filters as possible so that in any case in future your Query does not returns more than 5000 items...
9. Also consider lookup columns threshold values. if you wanted to retrieve more than 8 lookup columns in single query it will not work. you have to make multiple request to get data for more than 8 lookup columns.

And other general suggestion, make sure you spend quality time in designing list and information architecture before jumping into implementation.