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?