History table

Databases
1

I want to capture when a user logs in/out.

create table user_history (
   user_id SMALLINT NOT NULL,
   logged_in TIMESTAMP,
   logged_out TIMESTAMP,
   FOREIGN KEY ( user_id ) REFERENCES users (user_id)
);

When a user loggs in I

INSERT INTO user_history (user_id, logged_in) VALUES ( 1, now());

But what would I run when he logs out?

UPDATE user_history SET logged_out = now()) WHERE ?

Do I need to set a PK in order for the WHERE clause to work?

2

That is what I do. Save the PK in memory when logging in and update PK when logging out.

3

You need to take into account the times when the user doesn’t log out but gets logged out when their session expires

4

Why do you want to capture that? What’s the goal you’re trying to achieve?

1 Like
5

well, yes… something to allow you to identify a specific row… PKs were invented for just this reason

now ask yourself… what if a user logs in and then logs out and then logs back in again? what will you do then?

1 Like
6

Yes, you’re right.
I’ll have a PK in the user_history table and store it in a SESSION variable, then use that value for the update thing when logging out
I’m doing this for auditing who logs in

7

and if the session dies before the user logs out?

and what will you do when the user logs out and then logs back in?

what’s your PK?

8

shouldn’t the PK be an autonumber

What do I do if the session dies before the user logs out?

9

i suppose it could be, sure

you may think you’re at step 3 but you really are still at step 1 –

  1. get idea for log audit application
  2. design a table
  3. ??
  4. profit!
10

You do not specify what database (such as MySQL) or what you mean by login and logout; do you mean log into the browser or the database?

If it were me I would check to see if something exists. For example MySQL logon and logoff trigger for auditing | FromDual might help.

11

Do I need to set a PK in order for the WHERE clause to work?

Yes, you should. For effective RDB work developer should to realize at least three normal forms…

PK is actually second of them.

Otherwise potentially you will have a lot of problems.

12

2NF says the PK should be a single column (i’m paraphrasing)

i believe PK is 1NF

13

To satisfy 1NF, we need to ensure that the values in each column of a table are atomic.

PK should be single or planity of columns. Important that value(s) of PK columns are unique for any table record.

14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.