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

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

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

Post 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!
Last edited by Skara on Sat Jan 16, 2010 5:14 pm, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

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

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

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

Post by Skara »

Ahh.. Awesome idea. Thank you very much!
Post Reply