How to write "Last Seen" logic like that on Stack Overflow

Question

I'm working on an application that has similar logic as SO with regards to when the user was last seen. I've run into a conceptual problem that I'm hoping some of you Guru's can help me out with.

All activity is logged in an ActivityLog table in the database

When a logged in user hits the site and a new session is created, I update the activity log with the UserID and some very generic info. Same thing happens when they create a new record, update their profile, etc.

The problem I'm having is this.

If I use the most recent activity item, then navigate to my personal account page, the "Last Seen" shows up as 1 second ago because I JUST hit the db on session start... This is not good because I want to see what I was "last" there, not when I'm there "now".

However, if I use Skip(1).Take(1) to get the second record in the database, then when someone else views my profile while I may have "just" signed on... they'll see that I was on say a week ago and not today.

What kind of logic would you use in order to have your cake and eat it too?

I'm using ASP.NET MVC2 and Linq to SQL, but I think this question is more language agnostic.

Manzano

Answer 1

It sounds like you could just show the second most recent record for the current user, and for all other users show the most recent one.

Coalition

is it more simple and less system overhead to put current and last in the user table as well as retain an activity log? This sounds like more work overall since I have to maintain two record locations. I'm using the activity log for other things as well, so I don't want to remove it. – Manzano

also, since I am using LINQ to SQL with an IQueryable repository layer, I don't think that the retrieving of the data out of the Activity log will be too system intensive even if the log grows large. The search basically does an OrderBy and a Select Top 1 (or Second) – Manzano

also, also... Just to mention, I do think this might be the easiest answer so far... the part about displaying SECOND for current user and FIRST for all other users. – Manzano

@rockinthesixstring Glad you liked the idea! Well, as for the idea of storing current and last in a user table as well as retaining the activity log, I think your instinct is probably right, especially if you're pretty sure it won't be intensive. Premature optimization is a sin after all! :) – Coalition

@rockinthesixstring I've actually just edited that out of my answer. It would only make sense if you had a truly vast amount of activity data, and like I said, it smells of premature optimization. – Coalition

Answer 2

What I would do (simply to avoid a large logic loop) would be to add two fields. current_seen and last_seen. On login move current_seen to last_seen and set current_seen to the current timestampe. Then display last_seen as their "Last seen on XX/XX/XXX".

Strafe

I'm using OpenID and actually keeping the user logged in until they logout. This means that it's every time a new session is created that this process would need to occur (or whenever they do other activity on the site). – Manzano

Also, this method of thinking is pretty much exactly the same as the Skip(1).Take(1) that I mentioned above (if I'm only displaying the "Last Seen" portion at any given time. – Manzano

Answer 3

One place to look is at the source code to OSQA (the open source q&a system) -

http://www.osqa.net/

And yes, it looks a lot like StackOverflow (to say the least).

Wagoner