Multiple tables entry with current timestamp

Question

I have several different tables that the user can perform an action on, for example:

• Vote
• Comment
• Chat

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?

Answer 1

I would suggest creating your actions table with a timestamp field using a default of current_timestamp, and have the other tables use regular timestamp fields with no default value.

Then, after inserting a new row into your actions table, you can retrieve that row and insert the required rows into the additional tables using that returned value to populate the relevant fields.

In this way, the values between the tables will match as you require.

You may require an auto-incrementing field in your actions table, and then use the SQL command SELECT LAST_INSERT_ID(); within the same connection to get the auto-incrementing id from the actions table, allowing you to then perform another SELECT in order to get the timestamp value.

It will add a few queries to your overall insert, but will achieve the desired result.