Page 1 of 1

How best to track "User has seen this post" :: solved

Posted: Fri Jan 15, 2010 5:16 pm
by Skara
Ok, so I had a site that had 5 users and it's grown considerably--wasn't expecting this. Scaling just became a big issue.

How it is now...
Currently, if a user looks at a photo, it checks a `seenit` column to see if it finds the text "u{$login_id};"
If it finds it, the user has seen the photo. If not, it adds that text to the end--s/he's obviously seen it now.
In other words, `seenit` typically looks something like this: "u4;u8;u2;u1;u9;" etc..
Isn't a problem with 5 users and it still seems to work with 13, but I expect it to get to 50 soon, possibly hundreds in time or even more.

My question is, what is the best way to store whether a user has seen a particular photo? Also whether the user has seen the photo since a new comment has been posted.
I'm thinking a separate table...

`photos` => id, name, etc
`photos_seenit` => photo_id, user_id, status


Where `status` might be 0 for hasn't seen it, 1 for has seen it, 2 for has seen it, but a comment has been posted on it the user hasn't seen.

So my real question is... would this be scalable for up to a few hundred users or more? Remember that every time a comment is posted the following query would have to be run:
UPDATE photos_seenit SET status='2' WHERE photo_id='$photo_id';
Where that might modify a few hundred rows at the outside.

Would this be a significant slowdown? Is there a better solution?

Hopefully I'm making sense. Thanks for your help!

Re: How best to track "User has seen this post"

Posted: Fri Jan 15, 2010 6:03 pm
by VladSun
You may use the existence of the "have-seen-it" record as a "status".
I.e. you need tables structure like this:

Code: Select all

`photos` => id, name, etc
`photos_seen` => photo_id, user_id
When a user opens a picture you create a record in the second table. And your approach would require users_number*photos_number number of records.

To find out pictures seen by particular user you need an inner join for the 3 tables (users, photos, photos_seen).
I'll left the "find-pictures-not-seen-by-a-user" as your homework ;)

Re: How best to track "User has seen this post"

Posted: Fri Jan 15, 2010 6:17 pm
by Skara
Thanks!
Ok, yeah. I'm taking it from your response that this is an acceptable way to go, however I included the `status` for a reason. I'd like three different "seenit" statuses. First you haven't seen it at all, second you have, third if you've seen the photo but there is a new comment on it you haven't seen.

Therefore, I could use the existence as a first status (which would actually be a better solution than what I had), but I'd still need a third column to store whether the user has seen the photo but not new comments.
`photos_seen` => photo_id, user_id, (bool)new_comments

I suppose my question then becomes: Is this still properly scalable when I have to update all `photos_seen` rows that match a photo_id whenever a comment is posted?

Re: How best to track "User has seen this post"

Posted: Fri Jan 15, 2010 8:00 pm
by VladSun
You should have something like this:
`photos_seen` => photo_id, user_id, last_seen_comment_id
`comments` => id, photo_id, text
if comments.id is autoincrement you could easily check (by using MAX()) whether photos_seen.last_seen_comment_id is the last comments.id for a particular photo. Or you can use timestamp columns instead.

Re: How best to track "User has seen this post"

Posted: Sat Jan 16, 2010 5:13 pm
by Skara
Ahh.. Awesome idea. Thank you very much!