I have a facebook 'like' application - a virtual white board for multiple 'teams' who share a 'wall' common to that project. There are about 9-12 entities for which I capture the data. I'm trying to have the user's homepage display the update of activities that have happened since the past login - like how facebook posts notifications:
"[USER] has done [some activity] on [some entity] - 20 minutes ago"
where [...] are clickable links and the activities are primarily (rather only) CRUD.
I'll have to persist these updates. I'm using MySQL as the backend DB and thought of having an update table per project that could store the activities. But it seems there needs to be one trigger per table and that would just be redundant. More so It's difficult to nail down the tabular schema for that update table since there are many different entities.
The constraint is to use MySQL but I'm open to other options of "how" to achieve this functionality.
Any ideas?
PS: Using jQuery + REST + Restlet + Glassfish + MySQL + Java
It doesn't have to be handled at the database level. You can have a transaction logging service that you call in each operation. Each transaction gets a (unique, sequential) key.
Store the key of the last item the person saw, and show any updates where the key is higher, the update the last key seen.
A periodic routine can go through the user accounts and see what is the lowest seen transaction log key across all users (i.e. what is the newest log entry that all users have already seen) and delete/archive any entries with a key <= that one.