How best to track "User has seen this post" :: solved
Posted: Fri Jan 15, 2010 5:16 pm
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!
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!