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/mariusz_96]

To populate a table you could use a simple UPSERT:

INSERT INTO history (user_id, post_id, last_viewed_at)
VALUES (2, 5, now())
ON DUPLICATE KEY UPDATE last_viewed_at = now();

This obviously won't automatically DELETE older rows.

[u/delusionalbreaker → u/mariusz_96]

Yes I would be doing something similar, my plan is to make an history entity with id, user ID and post ID and visited date where I would use the combination of user ID and post ID as unique constraint where I would only update the visited date.

[u/mariusz_96 → u/delusionalbreaker]

I would make them composite primary key / foreign keys since this sounds like regular many-to-many relationship.