Keeping track of users and their actions

I;m trying to keep track of what users (logged in) are doing (actions they complete) when they are logged in
I’m thinking ill need
A table to hold the users
A table to keep track of each session
A table to link each action to a session,
and lastly a table of nothing but actions


create table users (
   user_id SMALLINT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50) NOT NULL,
   email VARCHAR(50) NOT NULL,   
   password VARCHAR(50) NOT NULL,
   created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
   PRIMARY KEY ( user_id )
);
create table users_history (
   session_id VARCHAR(50),
   user_id SMALLINT,
   logged_in TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   logged_out TIMESTAMP,
   PRIMARY KEY ( session_id )
);
create table users_actions (
   users_history_id INT NOT NULL AUTO_INCREMENT,
   session_id VARCHAR(50),
   action_id INT,
   FOREIGN  KEY ( action_id ) REFERENCES users ( action_id ),
   FOREIGN  KEY ( session_id ) REFERENCES users ( session_id ),
   PRIMARY KEY ( users_history_id )
);

create table actions (
   action_id INT NOT NULL AUTO_INCREMENT,
   action VARCHAR(50) NOT NULL,
   PRIMARY KEY ( action_id )
);

Is that the correct setup?

what happened when you tested it? ™

1 Like

Those actions don’t occur on cached pages do they?

What are you defining as actions? Manipulating a DB? Navigating the site?

Google has already solved this problem. Just add the tags to your site. Authentication is part of the api.

https://developers.google.com/analytics/devguides/collection/analyticsjs/custom-dims-mets

im defining an action as manipulating the database (like added a device)

In that case, you want to create audit tables and use Triggers.