I have several different tables that the user can perform an action on, for example:
Each of the above tables will have a timestamp column, to hold the date of creation (it is set to the current time).
I am using long polling to get real-time updates for other users that require these updates.
I assume it would heavier on the database to constantly search multiple tables for rows where time values are greater than some value. The number of these kinds of tables can increase if features are added.
So, I want to create a dummy table called actions, which will contain all the actions that a user performed. There will be another column in that table that will contain what the type of that action is, for example vote, comment, chat etc.
It will also contain a timestamp value, so that my long polling can just search in this actions table for any actions.
If there are actions present that are newer than a certain timestamp, then go to those action type tables (vote, comment, chat), and get rows that are newer than that same value, and return them to the user.
My question is: As I am inserting data to two different tables (and I want both of the timestamp values to be exactly equal), what should I do to maintain this integrity?