I'm looking at database architectures that will allow the storage of a list of items a user has already seen so that I could subsequently query the items table and only show the user items they had not already seen.
It seems like something that's been done before many times but I think I'm using the wrong search terms to find some good information on it. I tried to keep it vague so this might be useful to future searches.
The non-scaleable option would be to put this all in a relational database like MySQL and have a 3rd table alongside
items which would be
seen_items which could have a user_id and an item_id. The select would then just discard anything from the
seen_items table for the user_id in question.
I'm trying to look at ways this could scale when we're talking 1 million items and 100,000 seen items for 5 million users, to pull numbers out of the air. The other consideration is that each time something is "seen" it needs to be written to the database making it more write-heavy than read-heavy. My initial thinking is that this lends itself to a distributed NoSQL solution since a
seen_items table of 500,000,000,000 rows isn't exactly going to work out, but then could a NoSQL solution store a list of 100,000 IDs per user and ensure those IDs don't turn up in a search for a list of items?
Thanks for any thoughts or reading material you could provide!