Login - Logout Game

Hey Guys,

I am back with a new thing. I had tough times maintaining the user LOG and see when they come or go. I developed one small implementation to solve this. Hope it will help you guys...

So if you want to track the Login-Logout of users on your website, try this simple logic.

Create two tables in the database...

1. Users Master
2. User Access Log

User master shall hold UserID, username, password etc. But there has to be two additional fields.
a. LastAccessTime (type: datetime)
b. LastAccessIP (type: varchar)

In UserAccessLog, keep following fields (and no additional fields)
a. RowID (numeric - Identity [Auto incremental] Primary Key)
b. UserID (mapped to user master table)
c. AccessTime (type: datetime)
d. AccessIP (type: varchar)
e. AccessType (type: varchar; values: IN OUT)
f. remarks (type: varchar; this is an optional field reserved for future use)

Now,
When a user logs in, insert a row in AccessLOG. Remember, you should never run the "UPDATE" query during login/logout if you need good performance. Only use insert query on LOG. You can use SELECT also, because there is no other bypass to this one.

So, when user logs in, make an entry with Access Type as "IN". Also, update the Users Master and set the lastAccessTime to current time and LastAccessIP to the IP Address of user.

Now, in the function, where you check for session/cookie, add a small line that updates the user master and reset LastAccessTime to current time. In this manner, you will hold the latest click of every user. Remember, this logic can be very helpful if you have few hundered users. But if you are playing with several thousand users, this detoriate the performance of site.

When user clicks logout, simply insert a new row in LOG table with AccessType set to "OUT". Also, clean both additional fields (LastAccessTime, LastAccessIP) from the user master table.

Now the tricky part,

How to track LOGOUT if user closes browser without clicking the "logout" button.

It is not difficult now. All you have to do is, whenever a user logs in, you simply select the MAX(AccessTime) from Log file for a particular user and see its AccessType. If that accesstype is not "OUT", it would mean that he/she did not logout properly. If no record is returned, than this is the first time login, so no need to bother.
But if last entry is not OUT, simply check the user master and you will get the lastaccessIP and LastAccessTime from there (which will hold the time of his/her last click). Now you can update your LOG file from Master File's fields.
If your master file is also having empty fields, than you are screwed. It means that there is some problem with your code. Now just put some random values to LOG file to complete the entry.

I have been using this implementation in several applications for more than an year. I have tested this on several type of users varying for a few dozen to several hundered. This implementation is working perfectly for me :)

If you have a better implementation for this problem, please share with me. I would love to update my logic.