Database structure for visits

[phppup]

I'm considering setting up a webpage and tracking logins.

For simplicity, let's say an account is required with an email address.

So, [email protected] cannot do anything until he created his account. Now I want to know how often he logs in.

Is there a recommended or best practice to gathering and storing this data before it becomes cumbersome or unuseful?

Or is it as simple as connecting a table that associates a timestamp with every login by a registered user and just letting it run? (I'm wondering if there's something more efficient and less storage reliant)

[Barand]

Logging every login by a registered user is the favoured option.

Not only does it give you an audit trail of the logins but it adds functionality, such as being able to count logins in different time periods or determine most popular login times etc.

[gizmola]

Done this for many systems: 100% agree with Barand.  I will go one step further and make this an "event" table where the system can insert rows for other events.

Off the top of my head other events (in an event_type table or enum) would be a list like this:

 

• login
• logout
• bad password attempt
• change password
• reset password request

etc.

A simple table like this is common, has and has the benefit (with proper indexing) of allowing for the types of analysis and controls Barand listed.  It also allows for mitigating brute force password attempts, as you can use this table to limit the number of login attempts within a given time period for a specific user, and lock the account after a certain number of attempts.

Beyond the relationship to the User table (by Id) and a timestamp, you also typically want to store the IP address of the client.  If it's mysql, the best way to do this (and natively support both IPv4 and IPv6) is to store the IP as VARBINARY(16) and use the INET6_ATON() and INET6_NTOA() functions to convert the IP when storing and retrieving.  

Small tables like this, with a clear design scale very well, as MySQL/MariaDB (using the InnoDB engine) is tuned to maximize Select & Insert concurrency.  Often people will attempt to use a column or 2 in the user table, which they repeatedly update (ie. "last_login") which reduces concurrency, and is also less valuable than having a full history.

 

[phppup]

@gizmola Yes, I had considered IP tracking for the types of instances that you mentioned. Thankfully, this isn't (as of yet) for users that would be ill-intentioned (but it's good to know the expansion potential).

As a side note, recording the time of logout seems reasonable when the user clicks the button. But what is the process of the user simply shuts down the browser?

[gizmola]

On 7/11/2025 at 5:43 PM, phppup said:

@gizmola Yes, I had considered IP tracking for the types of instances that you mentioned. Thankfully, this isn't (as of yet) for users that would be ill-intentioned (but it's good to know the expansion potential).

As a side note, recording the time of logout seems reasonable when the user clicks the button. But what is the process of the user simply shuts down the browser?

Absolutely, user's often don't logout intentionally, so you can't depend on that event being recorded.  In general, you should be interested in any attempted change to their profile or other "escalation of privilege" or change to the core authentication mechanisms (password reset, password change).  Many systems will also include and require a 2nd factor authentication at registration, which unless it's a mobile app, will typically be email.  So that's another couple of event types you want to log (email authentication failure, email authentication re-request, email authentication success).

Even if you are not prepared to make use of IP logging initially, I'd recommend creating the column in the table as analysis of most events you want to be concerned with (like brute force attacks) will necessitate IP logging if you want to understand where the attacks or coming from, or building in automatic countermeasures like time based IP bans.