数据库设计可跟踪自上次访问以来的更新
我正在为论坛类型的应用程序设计数据库。用例涉及向用户显示未读主题。因此，我需要跟踪每个用户 ID 的每个帖子的已读/未读状态。
我有用户和帖子表。我想我应该使用 userid 和 postid 创建“Read_Posts”表，这样我就可以将用户看到的每个帖子添加到该表中。但是，随着时间的推移，这个表将变得非常巨大。
还有其他替代方法吗？
谢谢！
I am designing database for a Forum kind of application. The use cases involves showing unread topics for an user. So, I require to keep track of read/unread status for each post for each user id.
I have users and posts table. I am thinking I should created 'Read_Posts' table with userid and postid, so I can add each post user has seen to this table. But, over time this table will become very huge.
Any other alternate approaches to this?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你的设计看起来是正确的。
您也许可以考虑此功能的过期 - 也就是说，任何超过一周的帖子（或其他）都可以从该表中删除（无论是否存档）。
或者每个用户最多 10 个未读帖子...
Your design seems like the right one.
You can perhaps consider an expiration for this functionality - that is, any post that is over a week old (or whatever) can be expunged from this table (either archive it or not).
Or a maximum of 10 unread posts per user...
您可以尝试以某种方式压缩信息。仅当用户登录时，您才需要向用户显示他是否已阅读帖子，因此您应该将信息存储在用户附近的某个位置。用户可能会以日期排序的方式浏览您的帖子，因此近日期的读取帖子信息应该位于附近，以便有效地缓存和读取许多值。
尝试使用以下结构的表：
根据您期望的帖子数量，您可以使用周或天而不是月份。如果您在应用程序中缓存该值，则在显示许多帖子时可能会发现性能有所提高。
You can somehow try to compress the information somehow. You need to show the user if he has read a post, only if the user logs on, so you should store the information somewhere near the user. The user might look throught your posts in a date sorted way, so read-post-information of nearly dates should ly nearby, for efficient caching and reading of many values.
Try a table with this stucture:
Depending on how many posts you expect, you might use week or day instead of month. If you cache the value in your app, you might see a performance increase when displaying many posts.
对于每个用户，您可以在他们注销时（或在他们执行的每个操作时）存储最大的 post_id。然后，当他们返回时，任何 id 大于该值的帖子都会被标记为新帖子。
它不考虑他们在上次访问时未阅读的帖子，但会给出自上次访问以来的新活动的简单指标。它只需要在用户表上增加一个额外的列，因此影响应该相对较小。
当然，这假设帖子具有递增整数作为其 ID
For each user you could store the greatest post_id when they logoff (or at each action that they take). Then when they return, any posts with an id greater than that are flagged as being new.
It doesn't account for posts that they didn't read on their previous visit, but would give a simple indicator of activity that is new since their last visit. It would only need an extra column on the users table, so should be relatively low impact.
This assumes that posts have incrementing integers as their ID of course