Building a "topic read" function

[PC Nerd]

Hi,

 

Im building myself a mini CMS sort of thing, and i wanted to add a feature where the system can tell you ( and sort by) posts that oyu have or have recently read - similar to this forum and many others where unread posts appear in bold or other distinctive formatting.

 

Im just not 100% sure about how to build the Database/ php(theory) behind this.  Im not looking for a direct code sample, just a pointer/push in the right dcirection. 

 

Im currently thinking that i would have to have a  table called "read_Posts" or something ismilar, and in it woudl have the following feilds:

 

Read_ID auto, int (unique identifier)

Post_ID, int (reference to post)

Date, timestamp (when was it read).

User_ID, int (who read it)

 

and then when reading a pst, I would have to search to see if there is a currently "read" record, and if there is rewrite the timestamp to now, otherwise i would create a new one.  Then when sorting by posts, I would have to either combine tables in teh SQL query, or I would be able to simply post by ordering by read etc etc etc???

 

my only issue for this approach is that if there are 50 posts, and 50 users, thats already 250 rows that i could have potentially in my database.... so this table would become unmagagable soon. - so is this the only way or is there a more productive method?

 

 

Thansk in advance,

PC Nerd

[Naihab]

Well, your proposed solution is the obvious one to me, but perhaps there's a better one out there  - why not have a look at the source code for SMF or PHPBB or something and see how they do it?

[PC Nerd]

IVe checked out SMF.... and was rather confused by its layout.  im currently goign through the source code to it to see how it does it.

[ninedoors]

Could't you have a column in your "posts" table that would hold a string for all the members(their member id) that have read the post.  You could pull this string and search it to see if a member was 'in' the string, if not it woudl be unread.  Probably a better way but I think that would cut down on your DB size as you would just have to have one extra column in an already existing table.

 

Nick 

[PC Nerd]

Im not sure about the conversion or scale here, but i beleive its better to ave a larger database of smaller fields ( because of indexing for earching etc)... instead of a ( one column/table) smaller one with a very long column ,that is being called every second or so in a forum like environment.  besides, if i only want ot list those that havent been viewd by the specivif user... hten it would require me pulling every record out and searching it with PHP... ( or can i do somethign like in SQL: "WHERE field LIKE %Username%".... ( but then thats not scalable when writinga lot to it to remove usernames)???

 

thats an interesting solution there, and thanks, however personaly feel that that is trying to place a flat file setup into a databse field, and i fear it wouldnt work? what does everyone else think?