How to display seen and unseen content for each user

Question

I am trying to find an efficient technique to differentiate between seen and unseen content per user. I have a table of specific files, but need to keep track of the user who accessed them and then display a notification if new files or content has been added.

I have considered creating a table that just keeps a member and file id and whether the record exists / has been seen and joining that table onto the files table, but I see that table growing exponentially quicker with every file change. Would a JSON decoded file be a better approach for every user who logs in? Or any other recommendations?

Edit: Making a DB to track timestamps of when the user seen the file, so if the file is updated, I will know that the user has not seen the updated file.

Answer 1

If you have M members storing F files, you'll need to store M × F rows in the general case.

Adding another member costs you F additional rows, and adding a file costs M additional rows. This is much much less than exponential growth.

The "seen" function is monotonic with time - once seen, a file never becomes unseen. So in the limit, most of those rows will be storing boolean True to indicate "seen". This lets you conveniently compress / summarize with a timestamp, by storing that a given member has seen all files older than a certain timestamp.

You can choose to relax the problem's constraints by only allowing unseen files to be read in reverse chronologic order, so rather than storing F bits for a member you store just a single timestamp.

You can choose to relax the problem by keeping detailed track of just a fixed window of K=100 messages near the seen/unseen border. A user who chooses to scroll outside the K-file window would automatically advance the window so older files get the summary marking of "seen" by advancing that timestamp. Then storage is reduced from M × F down to M × K, independent of however many millions of ancient files are in the system. The window would have a summary timestamp on the old side, and another on the new side, representing e.g. the large number of new files Alice hasn't seen when she has not used your system for some months.