Page 1 of 1

Tracking Viewed Items

Posted: Wed Jan 14, 2009 3:29 pm
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.

Re: Tracking Viewed Items

Posted: Wed Jan 14, 2009 3:39 pm
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.

Re: Tracking Viewed Items

Posted: Wed Jan 14, 2009 3:42 pm
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?

Re: Tracking Viewed Items

Posted: Wed Jan 14, 2009 3:58 pm
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 :)

Re: Tracking Viewed Items

Posted: Wed Jan 14, 2009 4:01 pm
by Eran
+1 vlad

Re: Tracking Viewed Items

Posted: Wed Jan 14, 2009 4:32 pm
by Syntac
Perfect, thanks.

* Syntac gives Vlad an e-cookie