php - 数据库结构跟踪用户是否打开特定内容的最佳实践？

Question

目前我建立了一个网站，其中包含大量事件和非事件内容。内容发布后，它会处于事件状态，一段时间后就会变得不活动。我想跟踪所有在活跃期间打开内容的用户。在数据库中存储这些信息的最佳方式是什么？

我需要查询数据库以下问题:1. 哪些用户打开了特定内容？管理员很少查询此内容，每个内容可能会查询两到三次。2. 特定用户打开了哪些事件内容？3. 特定用户打开了哪些非事件内容(打开时处于事件状态且同时处于非事件状态的内容)？这两个查询的查询次数都比第一个查询更频繁(可能每两到三周一次)。用户可以查看他/她的个人资料，他/她打开了哪些内容。

首先我想到了一个包含三列的数据库表:“用户ID”，“内容ID”，“状态”。其中“status”对于事件内容为 true，对于非事件内容为 false。 “用户 ID”和“内容 ID”是内容表中的主键，“状态”是外键。但后来我想到了这张 table 的大小。在某些年份(希望)可能有 200 万个节点和 25 万个用户，因此可能有 5000 亿行......我认为，这会严重降低性能。

因此我想到了另一种方法:我创建两个数据库表，一个名为“content_opened_by_users”，另一个名为“user_opened_content”。前者将包含两列:“content id”和“users”。其中“users”是一个包含所有用户id的序列化数组，它在同一行中打开相应的内容id。第二个表将包含三列:“用户 ID”、“事件内容”、“非事件内容”。其中“事件内容”和“非事件内容”也是包含相应用户打开的事件/非事件内容的内容ID的序列化数组。因此，每当用户打开内容时，服务器都会从“content_opened_by_users”表加载相应的“users”数组，并从“user_opened_content”表加载相应的“事件内容”数组。如果用户 ID 尚未存在于“users”数组中，则将添加该用户 ID，如果内容 ID 尚未存在于“事件内容”数组中，则也将添加该 ID。然后数据库中的两个数组都将被覆盖。现在，如果我查询打开某个内容的所有用户，我会从“content_opened_by_users”表中获取一个数组。如果我查询特定用户打开的所有事件/非事件节点，我会从“user_opened_content”表中获取“事件内容”和“非事件内容”数组。然后我检查“事件内容”数组是否包含一些同时处于非事件状态的内容 ID，并将它们传输到“非事件内容”数组并将它们传回数据库。

我知道，我在这两个表中创建了冗余数据，但我希望这会提高性能。

那么，这是完成用户跟踪的便捷方法吗？或者可能有另一种更有效的方法吗？

我欢迎每一个建议!非常感谢。丹尼尔

Answer 1

首先，您可能想阅读 database normalization .

为了跟踪您所描述的信息，我将使用单独的表来表示用户、内容和访问权限，如下所示:

table    | columns
-------------------
users    | id, login, ...
content  | id, title, active, ...
access   | id, user_id, content_id, timestamp, ...

然后，无论谁打开哪个项目，您都可以存储用户和内容项目。 access 表包含有关哪个用户在何时打开哪个内容项的条目。这使您可以跟踪大量统计信息，而无需存储有关用户未打开项目的任何信息。

主要查询是:

1. 哪些用户打开了项目

   SELECT DISTINCT users.login FROM access JOIN users ON access.user_id = users.id JOIN content ON access.content_id = content.id WHERE content.title LIKE '%test%'
   

2. 用户打开的事件内容

   SELECT DISTINCT content.title FROM access JOIN users ON access.user_id = users.id JOIN content ON access.content_id = content.id WHERE content.active = true AND user.login = 'testuser'
   

3. 用户打开的非事件内容

   SELECT DISTINCT content.title FROM access JOIN users ON access.user_id = users.id JOIN content ON access.content_id = content.id WHERE content.active = false AND user.login = 'testuser'
   

   假设访问只能发生在事件内容上。要跟踪状态更改的时间，请添加另一个表，您可以使用该表来查找上次更改的时间戳并与访问条目进行比较。

请注意，这些查询根本没有优化。您还应该添加相应的 FOREIGN KEY constraints确保数据一致性并考虑添加额外的索引以提高性能。