Tracking Viewed Items

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Syntac
Forum Contributor
Posts: 327
Joined: Sun Sep 14, 2008 7:59 pm

Tracking Viewed Items

Post by Syntac »

So I'm currently writing what, for the sake of simplicity, we'll call a forum. Everything works great, but I now I need to know how to track whether a given user has viewed a certain thread. I have an idea for that; it involves storing a group of integer values in a field. Is there a special MySQL type for this sort of thing or do I just go with the ol' explode() trick?

Also, which schema is better for the above?

Code: Select all

forum_users
    ...
    viewed_threads
 
forum_threads
    ...
    viewed_by
I'm leaning toward the second one at the moment, since it eliminates the hassle of changing every user row when deleting a thread.
Last edited by Syntac on Wed Jan 14, 2009 3:41 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: Tracking Viewed Items

Post by VladSun »

Syntac wrote:I have an idea for that; it involves storing a group of values in a field. Is there a special MySQL type for this sort of thing or do I just go with the ol' explode() trick?
Don't do this! ;)
Keep your DB normalized.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Syntac
Forum Contributor
Posts: 327
Joined: Sun Sep 14, 2008 7:59 pm

Re: Tracking Viewed Items

Post by Syntac »

Ahh, do you mean splitting it off into a separate table? Shouldn't be too hard, come to think of it. Probably easier.

But before I go ahead with doing that, are there any caveats I should know about? Any hints?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Tracking Viewed Items

Post by VladSun »

You need an additional table with two fields: FK_user_id and FK_thread_id
If a record exists then the user has viewed the thread.
That's all :)
Last edited by VladSun on Wed Jan 14, 2009 4:42 pm, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Tracking Viewed Items

Post by Eran »

+1 vlad
User avatar
Syntac
Forum Contributor
Posts: 327
Joined: Sun Sep 14, 2008 7:59 pm

Re: Tracking Viewed Items

Post by Syntac »

Perfect, thanks.

* Syntac gives Vlad an e-cookie
Post Reply