Tacking number of users logged in over a time period - database design

In our application, we store the most number of users online. We do this by tracking what page each user is on currently and upon login, delete users who have been inactive for X minutes and then take a count of the users online, overwriting the most number online if this is higher.

How can we change this to track the number of users online over a time period, so we can look at a line graph of the number of users online. I guess we'll need a cron to run every 1/2/5/10/20/30/60 minutes to track the number online? Th开发者_StackOverflow中文版en how would be store this data, the table would get very large. I would think the likes of Statcounter/Analytics would have a better way of storing this data.

Any pointers would be appreciated.

There is no way to store it in less space. If you have a long period of time, you have a certain amount of data. It depends on the number of logins and length of intervals, whether you better store logins or samples of user count.

Just store it with the highest sampling rate you need, it's easy to group it for example by hour later:

SELECT YEAR(sample_datetime),
       MONTH(sample_datetime),
       DAY(sample_datetime),
       HOUR(sample_datetime),
       AVG(user_count)
FROM stats
GROUP BY 1,2,3,4

We have a table that stores logins, so we have a user_id column and login_time column.

However, you could extend this to cover your sessions and store a login_time, and logoff_time when a session ends. This way, you could then count the number of sessions in between a time range with a simple SQL query, for example:

SELECT COUNT(*) AS count
FROM sessions
WHERE login_time <= '$yourTime'
AND logoff_time >= '$yourTime'

Take a look at http://www.php.net/manual/en/session.customhandler.php on session custom handlers.

As you already mentionend, you'll have to store the time/value pairs somewhere, probably in a database table. All you need is the date/time and the value of online users (a table with 2 columns).

The amount of data points will depend on how often you'll want to query the counter. Typically, I don't think it will be useful to poll more often than what your inactivity threshold is - if you consider a user inactive after 10 minutes, I'd not poll the counter more often than every 10 minutes.

Though the amount of data should not be a problem for most databases, you may be interested in a Round Robin Database, or at least the concept of it. The idea is to have a very detailed counter (1 data point every 10 minutes) for the last week, a less detailed counter (every 4 hours) for the last month, and an even less detailed counter (every day) for the last year, and so on. Set up this way, you have a constant number of data points at any moment.