用于跟踪用户是否打开了某个内容的数据库结构的最佳实践 - Best practice for database structure to track if user opened a certain content?

目前我正在建立一个网站，该网站将包含许多活动和非活动内容。发布内容时，该内容处于活动状态，一段时间后将变为非活动状态。我想跟踪在活动期内打开内容的所有用户。在数据库中存储这些信息的最佳方式是什么？

我需要查询数据库以下问题：1.哪些用户打开了特定内容？这是罕见的查询，可能由管理员为每个内容查询两到三次。2.哪些活动内容是由特定用户打开的？3.哪些非活动内容（打开时处于活动状态，同时处于非活动状态的内容）是由特定用户打开的？这两个查询都比第一个查询更频繁（可能每两到三周查询一次）。用户可以查看他/她的个人资料，查看他/她打开的内容。

首先，我想到了一个包含三列的数据库表："用户id"、"内容id"answers"状态"。其中"status"对于活动内容为true，对于非活动内容为false。"用户id"answers"内容id"是主键，"状态"是内容表中的外键。但后来我想到了这张桌子的大小。几年后（希望）可能会有200万个节点和25万个用户，因此可能会有5000亿行。。。我认为，这会大大降低性能。

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

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

好吧，这是一种方便的方式来完成用户跟踪吗？或者可能还有另一种更有效的方法？

我欢迎所有的建议！非常感谢。Daniel

首先，您可能想了解数据库规范化的相关内容。

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

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

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

主要查询是：

哪些用户打开了项目

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%'

用户打开的活动内容

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'

用户打开的非活动内容
```
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约束以确保数据一致性，并考虑添加其他索引以提高性能。