database - 数据库中的一张大表还是许多小表？

Question

假设我想使用像 postgresql 这样的数据库创建一个典型的 todo-webApp。用户应该能够创建待办事项列表。在此列表上，他应该能够进行实际的待办事项条目。

我将待办事项列表视为具有所有者、名称等不同属性的对象，当然还有实际的待办事项条目，它们具有自己的属性，如内容、优先级、日期......。

我的想法是为所有用户的所有待办事项列表创建一个表。在此表中，我将存储每个列表的所有属性。但是出现的问题是如何存储待办事项本身？当然在另一个表中，但我应该:

<强>1。为所有条目创建一个大表，并有一个字段存储它们所属的待办事项列表的 ID，如下所示:

todo-list: id, owner, ...
todo-entries: list.id, content, ...

总共会给出 2 个表。 todo-entries 表可能会变得非常大。虽然我们知道条目会过期，因此该表只会随着使用量的增加而增长，而不会随着时间的推移而增长。然后我们会写类似 SELECT * FROM todo-entries WHERE todo-list-id=id 的东西其中 id是我们要检索的列表的。

或

<强>2。为每个用户创建待办事项表。

todo-list: id, owner, ...
todo-entries-owner: list.id, content,. ..

条目表的数量取决于系统中的用户数量。类似于 SELECT * FROM todo-entries-owner .中型表格，取决于用户总共输入的条目数。

或

<强>3。 为每个待办事项列表创建一个待办事项表，然后将生成的表名存储在该表的字段中。例如，我们可以在表名中使用 todos-list 唯一 ID，例如:

todo-list: id, owner, entries-list-name, ...    
todo-entries-id: content, ... //the id part is the id from the todo-list id field. 

在第三种情况下，我们可能有相当多的表。用户可能会创建许多“简短”的待办事项列表。要检索列表，我们将简单地沿着 SELECT * FROM todo-entries-id 行进行其中 todo-entries-id应该是待办事项列表中的一个字段，或者可以通过将“待办事项”与待办事项列表唯一 ID 连接来隐式完成。顺便说一句:我该怎么做，应该在 js 中完成吗？还是可以直接在 PostgreSQL 中完成？与此非常相关:在 SELECT * FROM <tablename> 中语句，是否可以将其他表的某些字段的值设置为 <tablename> ？喜欢SELECT * FROM todo-list(id).entries-list-name左右。

这三种可能性从几个大表到多个小表。我个人的感觉是第二种或第三种方案更好。我认为他们可能会扩展得更好。但我不太确定，我想知道“典型”方法是什么。

我可以更深入地探讨我对每种方法的看法，但要直奔我的问题:

• 我应该选择三种可能性中的哪一种？ (或其他任何事情，这与规范化有关吗？)

跟进:

• (PostgreSQL) 语句会是什么样子？

Answer 1

唯一可行的选择是第一个。它更易于管理，而且很可能比其他选项更快。

假设您有 100 万用户，每个用户平均有 3 个待办事项列表，每个列表平均有 5 个条目。

场景一

在第一个场景中，您有三个表:

• todo_users:100 万条记录
• todo_lists:300 万条记录
• todo_entries:1500 万条记录

这样的表大小对于 PostgreSQL 来说不是问题，使用正确的索引，您将能够在不到一秒的时间内检索任何数据(意味着只是简单的查询；如果您的查询变得更复杂(例如:给我 todo_entries for the最长的 todo_list 的前 15% 的 todo_users 在 3 个月的时间内创建了少于 3 个 todo_lists 并且输入的 todo_entries 最多)它显然会更慢(就像在其他场景中一样)。查询非常简单:

-- Find user data based on username entered in the web site
-- An index on 'username' is essential here
SELECT * FROM todo_users WHERE username = ?;

-- Find to-do lists from a user whose userid has been retrieved with previous query
SELECT * FROM todo_lists WHERE userid = ?;

-- Find entries for a to-do list based on its todoid
SELECT * FROM todo_entries WHERE listid = ?;

您也可以将三个查询合并为一个:

SELECT u.*, l.*, e.* -- or select appropriate columns from the three tables
FROM todo_users u
LEFT JOIN todo_lists l ON l.userid = u.id
LEFT JOIN todo_entries e ON e.listid = l.id
WHERE u.username = ?;

使用 LEFT JOIN 意味着您还将获得没有列表的用户或没有条目的列表的数据(但列值将为 NULL)。

插入、更新和删除记录可以使用非常相似的语句完成，而且速度也同样快。

PostgreSQL 将数据存储在“页面”(通常大小为 4kB)上，并且大多数页面都会被填满，这是一件好事，因为与其他操作相比，读取写入页面非常慢。

场景2

在这种情况下，每个用户只需要两个表(todo_lists 和 todo_entries)，但您需要某种机制来识别要查询的表。

• 100 万个todo_lists 表，每个表有几条记录
• 100 万个todo_entries 表，每个表有几十条记录

唯一可行的解​​决方案是根据与用户名相关的“基本名称”或您网站上的一些其他永久性身份验证数据来构建完整的表名。所以像这样:

username = 'Jerry';
todo_list = username + '_lists';
todo_entries = username + '_entries';

然后您使用这些表名进行查询。无论如何，您更有可能需要一个 todo_users 表来存储 100 万用户的个人数据、用户名和密码。

在大多数情况下，表会非常小，PostgreSQL 不会使用任何索引(也不必)。但是，它会更难找到合适的表，而且您很可能会在代码中构建查询，然后将它们提供给 PostgreSQL，这意味着它无法优化查询计划。一个更大的问题是为新用户创建表(todo_list 和 todo_entries)或删除过时的列表或用户。这通常需要您在前面的场景中避免的幕后管理。最大的性能损失将是大多数页面只有很少的内容，因此您浪费了磁盘空间和大量时间来读取和写入那些部分填充的页面。

场景 3

这个场景比场景 2 更糟糕。不要这样做，这太疯狂了。

• 300 万个表todo_entries，每个表有几条记录

所以...

坚持选择选项 1。这是您唯一真正的选择。