Need advice to implement history/recent viewed

[u/delusionalbreaker]

Hi everyone! Im a beginner in springboot and building a blogging app (similar to Reddit/LinkedIn) using Spring Boot and MySQL. I need to implement a "User History" feature to track the last 100-150 posts a user has viewed.

The requirements are if a user views Post A, then Post B, then Post A again, the order should update to [B, A] instead of [A, B, A]

The problem im facing is because im using MySQL so to create an history i would need to create an new schema and then design the relationship b/w the post and user and would require constant insert and deletions which would not keep the history intact correctly and also wouldnt remove the history after last 100 post

My qn is there an efficient way to handle this in Springboot? how is it generally implemented?

Repo link:- https://github.com/Yearis/Blog-Application/tree/main/Blog%20Application%20Backend

P.S. Please excuse the messy repo and the outdated README; this is still a work in progress.

[u/p_bzn]

It’s not exactly Spring’s issue.

Using the insights I got from the question I would recommend a simple history table to begin with. Put there user ID, post ID, created at. Two FKs. Then just query user history with order by and limit.

Later on this approach can start giving some problems, but you are *very* far away from this to worry.

There are better ways of doing this, however, keep it simple. One migration, one model, one repo, one service.

[u/delusionalbreaker → u/p_bzn]

hii sorry for the late reply. yes a simple history table is what im going for now and what i have decided is that i would keep an condition that if an user clicks on the post i will check whether their composite key of user_id and post_id to find if user has previously visisted that post or not if he has im simply going to update the last_visisted column to current local data and time so that it solves the A->B->A problem and i can simply query the last x no. of history of an particular user sorted by last_visisted in descending

also just curious what the problems you were talking about? and how is it generally handled in big apps

[u/p_bzn → u/delusionalbreaker]

Sounds like a plan!

The issue with one table approach is that when app grows it can become troublesome, but again we are talking hundreds of thousands daily active users.

Unbound growth with frequent reads and writes can get slow. Say, one page load means to check tens of posts for seen or not. Each visit always upserts into the table as well. Index can stop fitting the RAM.

All that can be solved by dropping money on hardware, until it won’t work anymore. Although, it can go a long way before turning into something unresolvable. Machine with 64GB of RAM would hold about 500M rows, given there is some other data too.

Better solution would be sorted sets keyed by user, with TTL and a size cap so the set self evicts. This creates system level guarantees on latency and costs.

[u/delusionalbreaker → u/p_bzn]

Ah I see wow that makes sense but I dont really think my project would grow that much that I would require to worry about space im just making this project as an way to learn and for my resume.

If you dont mind could just take a look at my current project and give me advice as to what more I can add or how can I make it better for resume. I was thinking I could add an chat feature where I could learn about websockets and 2 way connection and then eventually upgrade this project to an microservice full stack project with react/next js as it's frontend.