多表还是单表？ [英] multiple table or one single table?

问题描述

我已经看到一些论坛，但是他们不回答我想知道的一件事。我将首先解释我的主题：

I already saw a few forums with this question but they do not answer one thing I want to know. I'll explain first my topic:

我有一个系统，其中多个用户的每个日志都输入到数据库（例如User1登录，User2登录，User1进入用户管理，用户2更改密码等）。所以我预计每天每个用户100到200个条目。现在，我在一个表中做，并查看它，我只需要过滤掉使用UserID。

I have a system where each log of multiple users are entered to the database (ex. User1 logged in, User2 logged in, User1 entered User management, User2 changed password, etc). So I would be expecting 100 to 200 entries per user per day. Right now, I'm doing it in a single table and to view it, I just have to filter out using UserID.

我的问题是，这是更高效？我应该使用一个表还是每个用户创建一个表？

我担心如果我使用单个表，数千条目。我已经读了一些利弊使用多个表和一个表，特别是关于更新表。

I am worried that if I use a single table, the system might have some difficulty filtering thousands of entries. I've read some pros and cons using multiple tables and a single table especially concerning updating the table(s).

我也想知道哪一个保存更多的空间？多个表或单个表？

推荐答案

使用userId的索引，您应该能够轻松地扩展到数百万行，而且几乎没有问题。

I would go with a single table. With an index on userId, you should be able to scale easily to millions of rows with little issue.

每个用户的表可能更高效，但通常设计差。每个用户的表的问题是，它使得很难回答其他类型的问题，如昨天谁在用户管理？或有多少人改变了他们的密码？

A table per user might be more efficient, but it's generally poor design. The problem with a table per user is it makes it difficult to answer other kinds of questions like "who was in user management yesterday?" or "how many people have changed their passwords?"

至于使用的存储空间 - 我想说每个用户的表可能会使用更多的空间，之间的两个选项应该是相当小。

As for storage space used - I would say a table per user would probably use a little more space, but the difference between the two options should be quite small.

这篇关于多表还是单表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！