Should I use a separate table to store online users or should I just use additional fields in the extant members table?

Question

I'm working on an online system that allows users to interact socially and of course it will be important to be able to identify users that are in fact online. I know about HTTP being stateless and I know about using sessions, so I'll need to accomplish this by comparing the last active time of a user to an arbitrary expiration time.

My ultimate question comes down to this : Should I just add some fields to the existing members table (last_active_time, is_user_online, hide_online_status, etc) OR would it be best to maintain this information in a separate table? My initial thought is to use the existing table for simplicity. Aside for level of complexity, what are the benefits/disadvantages of one vs the other?

Answer 1

I would maintain this within a separate table. If you have have a million users and you want to know who is online, you do not want to be scanning that table over and over again to find that information. Your "online" table will be relatively small and you can have a job that scans it periodically for those that have not come in in the past 5 min or so and then simply delete them from the online table and update anything necessary in the members table for "last_seen"

Answer 2

Create a new table.

First, there's the philosophical reason, which is that one object (read: table) should have one purpose. That gets blurred a lot in database logical design, but it's a good principle nonetheless.

But the real reasons are from physical design: first, the number of users that will be logged on at any given time can be assumed to be far less than the total number of users, so that storage requirements will be lower. And lower storage requirements translate directly into reduced load on the database, simply because you have fewer blocks to read and update.

If you put this data in your main table, moreover, than those blocks will be scattered over a much wider space, and the contents of the blocks will continually grow and shrink as you change the data.

And finally, you'll probably require some indexes, for example to see who's online now. And those indexes will (1) take up even more space, (2) create even more scattered dirty blocks that have to be physically written to disk, and (3) introduce a point of update contention.

Answer 3

I think it depends on how many users you expect to have. If there are only going to be a couple thousand users, I would just make the online status an attribute of a user, in the user table. If there are going to be more than that, then you'll want an online_users table, and keep that information there, with a foreign key to the main users table. To reap the benefits (mentioned by above posters), you'll want a task to run every few hours or so to remove inactive users from the online_users table.