[eluser]jedd[/eluser]
I feel like a bit of a twat - I never noticed the date field on the 'Topic Title' (/viewforum/) page was a link to the latest post in that thread - I've always gone to the thread and scrolled down. Oh well.
I think we're agreed that the reference table needs to contain [ user_id , thread_id , message_id ] ... yeah?
I like the idea of storing post_id rather than date, as dates are longer and harder to handle, and contain more information than I need here.
I think that the link on the thread-list (as per my first paragraph) should take you to the next message after the one that's listed in the reference table for the user - and not the most recent message in that thread. This is a subtle distinction, and not hugely important, and I'm also unsure how to elegantly code using just SQL (look for the selected row *after* another identifiable row). I can come back to that.
I like Fuzzy's idea of a self-maintaining table - older posts are cleaned out based on x-number-of-tracked-threads-per-user, or on anything > 1 month, etc. However I think this will be expensive, especially if the check function is called on every page load.
Having said that, I also grok D13's point about setting aside, or even making an allowance for, a finite number of threads to track per user. I agree that it's not the most elegant approach.
One of the points I'm stuck on now is balancing this problem - tracking sufficient number of threads, for an appropriate length of time, without preserving an unreasonable amount of completely useless data in that table.
xwero - I'm curious on your take on handling (or rather minimising) the huge range of permutations here - CI forums as an example, with 80,000 users and this thread number is 110399 - so you only track the correlation once a user walks into a forum - I get that - but do you ever
untrack that connection? In reality, you (and the site) doesn't care that you read the last post in a thread that wound up in April 2006, right? You'll likely never hit a thread-list page that shows anything from that era.
Hmm .. I'm wondering if there's an algorithm in there to be had - something along the lines of any post older than (insert arbitrary figure - 2 months?) is considered read - and can consequently have any record of it removed from the db reference table. Again there's the problem of where and when do you clean this out for a given user, but that's slightly easier to solve.
