Although this could be answered with a number of database design ideas, there is a problem with the premise of the question as posed. The question as you've written it makes it apparent that you are looking to solve this problem on your own, but why reinvent the wheel, and especially, why reinvent it on the server?
Your first sentence says "It is pretty common for a web application ... to indicate to the current user whether they have already viewed the associated item."
This is true — it is common — but I've rarely (maybe never) seen simple click-visiting-status achieved through server-side databases. Almost universally it is accomplished using the native browser history, through the use of
:visited CSS pseudo-selectors.
How to do it, and how REST helps
- The user visits a page
- The browser records that page (URL) visit in history
- Any time a link's href matches a visit in history, the
:visited pseudo-selector will apply to that element.
will apply a red color to any visited link on a page (unless a conflicting style rule is more specific).
How REST helps
When you use REST URIs appropriately, you are more fully able to utilize the web browser's native features, including visited status, and other features such as caching.
For example, if your web application uses consistent URIs, then the browser is easily able to recognize whether you've viewed an item no matter where its URI appears throughout your application:
But, if you have poor, inconsistent URI creation, this is problematic. Here are some example URIs that break caching and visited links (based on common real-world patterns):
To the viewer, these links may all appear to point to the same page but the browser will have no knowledge of that.
If your application has this problem, it's probably because the URI has been overloaded in purpose to track things that it's really not meant to. But, it is probably still easier to fix this than to track "visited" state for every viewer in your database.
Why use REST and native browser features?
This pattern has many advantages.
Don't reinvent the wheel. Visited status already exists in browsers.
Using appropriate URIs is a server problem. Solve server problems on the server.
I can't bold this enough: Scalability (!!!) Viewing those URIs is a distributed problem. Always prefer to solve distributed problems on the client.
The complexity of this feature could absolutely explode if you attempt to solve it on the server. Think about it. If you have N visitors and M distinct resources, then you have a problem magnitude of N × M. You have to resolve the problem every time N or M (or both) outgrow your previous solution. Any time you have a new type of resource that you didn't account for you will need to do more fixing. Your ongoing resource costs will be higher as well.
If you use browser native features you don't have to do any of this. The problem is already distributed.
You are using the features of HTTP for the purposes they were designed for. URIs were designed to identify resources. Browsers are good at using URIs to get them, and servers are good at delivering them.
When does a server solution make sense?
Finally, there are a few cases where a server solution might make sense:
:visited support insufficient:
- If you need to change content more significantly than just colors, since the capability of
:visited has been reduced for the privacy of the user.
- Your users frequently have more complicated browsing behavior than can be served by the browser history, such as frequently changing devices
- "Recently viewed/edited items" or similar — display lists of only visited items.
- "Not viewed yet" or similar — display lists of only unvisited items, such as marking inbox items as read
- "Who has viewed this" — Tracking popularity or alerting other users to who has viewed a particular item, like LinkedIn's profile views feature.