unread/read design question

[Anon]

So I have this package that basically manages items. Some users would like to be able to tell if they've read one of the posts or not.

I've thrown out cookies.

What I was thinking was having a database table that stores which posts they have viewed. Anything that wasn't in the table would be marked as 'unread'.

I'm either going to make each user have a text field which will contain a sorted list of ids (each item has a unique id) or make a table that had user_id<->item_id pairings. Any thoughts to which would be better?

I'm currently using mysql.
Thanks.

[roel_v]

Depends on the size of your application. Having a seperate table is cleaner from a design point of view, however having 500000 viewing 10 messages a day is going to get ugly :-) If the size is manageable (sp?), I would opt for a seperate table, you can easily every once in a while delete all old viewed items, run some nice statistics on it, ....

[Anon]

It depends on the # of posts and the # users I would think.

For small number of posts/users using the user_id/item_id may be easiest for you, also easier to clean up deleted posts.

If you were to go with your first design, I would see if the method for usenet newsgroups was detailed anywhere. It uses a good method to track using ranges and that may be a bit more compact.

-Scott

[Anon]

Items will grow to several thousand or more so I'm going to lean away from using pairings.

I'm leaning towards the first method of using a text field to store a massive array of items. The way other stuff works I need to hit the user for some data on each page view anyways.

I'm also going to reverse the marked or not marked algorithm. Only unread items are in the list. This way users that keep up have less overhead and the data doesn't grow huge as the number of items grows.

I will have the overhead of having to update user records when a new item is inserted but there may be a reason for me to be doing so anyways. I just hope the text fields doesn't grow ungodly large.

This seems to make th emost sense to me.