I'm working on an online system that allows users to interact socially and of course it will be important to be able to identify users that are in fact online. I know about HTTP being stateless and I know about using sessions, so I'll need to accomplish this by comparing the last active time of a user to an arbitrary expiration time.
我正在开发一个允许用户进行社交互动的在线系统，当然，能够识别真正在线的用户是很重要的。我知道HTTP是无状态的，也知道如何使用会话，因此需要通过将用户的最后活动时间与任意过期时间进行比较来实现这一点。
My ultimate question comes down to this : Should I just add some fields to the existing members table (last_active_time, is_user_online, hide_online_status, etc) OR would it be best to maintain this information in a separate table? My initial thought is to use the existing table for simplicity. Aside for level of complexity, what are the benefits/disadvantages of one vs the other?
我的最终问题是:我应该向现有的members表(last_active_time、is_user_online、hide_online_status等)添加一些字段，还是最好将这些信息保存在单独的表中?我最初的想法是为了简单起见而使用现有的表。除了复杂程度之外，一个人与另一个人的优缺点是什么?
3 个解决方案
#1
1
I would maintain this within a separate table. If you have have a million users and you want to know who is online, you do not want to be scanning that table over and over again to find that information. Your "online" table will be relatively small and you can have a job that scans it periodically for those that have not come in in the past 5 min or so and then simply delete them from the online table and update anything necessary in the members table for "last_seen"
我将把它放在一个单独的表中。如果你有一百万用户，你想知道谁在线，你不想一遍又一遍地扫描这个表来寻找信息。您的“在线”表将相对较小，您可以有一个任务，定期扫描它，查找在过去5分钟内没有进入的表，然后从在线表中删除它们，并更新成员表中“last_seen”所需的任何内容
#2
2
Create a new table.
创建一个新表。
First, there's the philosophical reason, which is that one object (read: table) should have one purpose. That gets blurred a lot in database logical design, but it's a good principle nonetheless.
首先，有一个哲学上的原因，那就是一个物体(读作:table)应该有一个目的。这在数据库逻辑设计中被模糊了很多，但它仍然是一个很好的原则。
But the real reasons are from physical design: first, the number of users that will be logged on at any given time can be assumed to be far less than the total number of users, so that storage requirements will be lower. And lower storage requirements translate directly into reduced load on the database, simply because you have fewer blocks to read and update.
但真正的原因来自于物理设计:首先，可以假定在任何给定时间登录的用户数量远低于用户总数，因此存储需求将会更低。更低的存储需求直接转化为数据库的减少负载，这仅仅是因为读取和更新的块更少。
If you put this data in your main table, moreover, than those blocks will be scattered over a much wider space, and the contents of the blocks will continually grow and shrink as you change the data.
此外，如果您将这些数据放在主表中，那么这些块将分散在更大的空间中，并且当您更改数据时，这些块的内容将继续增长和收缩。
And finally, you'll probably require some indexes, for example to see who's online now. And those indexes will (1) take up even more space, (2) create even more scattered dirty blocks that have to be physically written to disk, and (3) introduce a point of update contention.
最后，您可能需要一些索引，例如，看看现在谁在线。这些索引将(1)占用更多的空间，(2)创建更多分散的脏块，这些块必须物理地写入磁盘，(3)引入一个更新争用点。
#3
0
I think it depends on how many users you expect to have. If there are only going to be a couple thousand users, I would just make the online status an attribute of a user, in the user table. If there are going to be more than that, then you'll want an online_users table, and keep that information there, with a foreign key to the main users table. To reap the benefits (mentioned by above posters), you'll want a task to run every few hours or so to remove inactive users from the online_users table.
我认为这取决于你希望拥有多少用户。如果只有几千个用户，我只会让在线状态成为用户的一个属性，在用户表中。如果有更多的内容，那么您将需要一个online_users表，并将该信息保存在那里，并使用一个外键到主用户表。为了获得好处(上面的海报提到过)，您需要每隔几个小时运行一个任务，从online_users表中删除不活跃的用户。
#1
1
I would maintain this within a separate table. If you have have a million users and you want to know who is online, you do not want to be scanning that table over and over again to find that information. Your "online" table will be relatively small and you can have a job that scans it periodically for those that have not come in in the past 5 min or so and then simply delete them from the online table and update anything necessary in the members table for "last_seen"
我将把它放在一个单独的表中。如果你有一百万用户，你想知道谁在线，你不想一遍又一遍地扫描这个表来寻找信息。您的“在线”表将相对较小，您可以有一个任务，定期扫描它，查找在过去5分钟内没有进入的表，然后从在线表中删除它们，并更新成员表中“last_seen”所需的任何内容
#2
2
Create a new table.
创建一个新表。
First, there's the philosophical reason, which is that one object (read: table) should have one purpose. That gets blurred a lot in database logical design, but it's a good principle nonetheless.
首先，有一个哲学上的原因，那就是一个物体(读作:table)应该有一个目的。这在数据库逻辑设计中被模糊了很多，但它仍然是一个很好的原则。
But the real reasons are from physical design: first, the number of users that will be logged on at any given time can be assumed to be far less than the total number of users, so that storage requirements will be lower. And lower storage requirements translate directly into reduced load on the database, simply because you have fewer blocks to read and update.
但真正的原因来自于物理设计:首先，可以假定在任何给定时间登录的用户数量远低于用户总数，因此存储需求将会更低。更低的存储需求直接转化为数据库的减少负载，这仅仅是因为读取和更新的块更少。
If you put this data in your main table, moreover, than those blocks will be scattered over a much wider space, and the contents of the blocks will continually grow and shrink as you change the data.
此外，如果您将这些数据放在主表中，那么这些块将分散在更大的空间中，并且当您更改数据时，这些块的内容将继续增长和收缩。
And finally, you'll probably require some indexes, for example to see who's online now. And those indexes will (1) take up even more space, (2) create even more scattered dirty blocks that have to be physically written to disk, and (3) introduce a point of update contention.
最后，您可能需要一些索引，例如，看看现在谁在线。这些索引将(1)占用更多的空间，(2)创建更多分散的脏块，这些块必须物理地写入磁盘，(3)引入一个更新争用点。
#3
0
I think it depends on how many users you expect to have. If there are only going to be a couple thousand users, I would just make the online status an attribute of a user, in the user table. If there are going to be more than that, then you'll want an online_users table, and keep that information there, with a foreign key to the main users table. To reap the benefits (mentioned by above posters), you'll want a task to run every few hours or so to remove inactive users from the online_users table.
我认为这取决于你希望拥有多少用户。如果只有几千个用户，我只会让在线状态成为用户的一个属性，在用户表中。如果有更多的内容，那么您将需要一个online_users表，并将该信息保存在那里，并使用一个外键到主用户表。为了获得好处(上面的海报提到过)，您需要每隔几个小时运行一个任务，从online_users表中删除不活跃的用户。