SQLite：每个用户使用单个表还是全部使用一个表？ [英] SQLite: Individual tables per user or one table for them all?

问题描述

我已经设计了一个使用SQLite数据库的网站。我没有使用一个大桌子，而是设计了它，以便在用户注册时为他们创建一个单独的桌子。每个用户可能会使用数百条记录。我这样做是因为我认为这样会更容易构造和访问。

I've already designed a website which uses an SQLite database. Instead of using one large table, I've designed it so that when a user signs up, a individual table is created for them. Each user will possibly use several hundreds of records. I done this because I thought it would be easier to structure and access.

在该网站上的其他问题上，我发现一个表比每个用户使用多个表要好。 。

I found on other questions on this site that one table is better than using many tables for each user.

重新设计我的网站是否值得，因为它没有很多桌子，而是会有一个大桌子？我目前的方法虽然仍在开发中，但似乎仍能很好地工作，因此我不确定它在实际环境中的堆叠效果如何。

Would it be worth redesigning my site so that instead of having many tables, there would be one large table? The current method of mine seems to work well though it is still in development so I'm not sure how well it would stack up in a real environment.

问题是：是否要更改代码，以便在性能，效率，组织和空间方面值得拥有一个大型数据库而不是许多单个数据库？

The question is: Would changing the code so that there is one large database instead of many individual ones be worth it in terms of performance, efficiency, organisation and space?

SQLite ：创建用户表。

CREATE TABLE " + name + " (id INTEGER PRIMARY KEY, subject TEXT, topic TEXT, questionNumber INTEGER, question TEXT, answer TEXT, color TEXT)

SQLite：正在添加

"INSERT INTO accounts (name, email, password, activated) VALUES (?,?,?,?)", (name, email, password, activated,)

请请注意，如果有任何不同，我将在Flask中使用python。

Please note that I'm using python with Flask if it makes any difference.

编辑

我也意识到已经存在类似的问题，但是没有人指出优点或缺点是否值得。

I am also aware that there are questions like this already, however none state whether the advantages or disadvantages will be worth it.

推荐答案

使用面向对象的语言，您会为每个用户创建一个类吗？还是每个用户都有一个类的实例？

In an object oriented language, would you make a class for every user? Or would you have an instance of a class for each user?

每个用户只有一个表是一个非常糟糕的设计。

Having one table per user is a really bad design.

• 您不能基于非用户名的任何字段搜索消息。使用当前的解决方案，您将如何查找某个特定 questionNumber 的所有消息？


• 您无法加入消息表。您必须进行两个查询，一个查询找到表名，一个查询实际查询表，这需要两次往返数据库服务器的操作。


• 每个用户现在都有自己的表模式。升级时，必须将模式迁移应用于每个消息表，如果某些表与其余表不一致，上帝会为您提供帮助。


• 使用外键指向实际上是不可能的到您的消息表。您无法指定外键列指向的表，因为它不会相同。


• 您的名称可能与当前设置冲突。如果有人使用用户名帐户注册怎么办？诚然，通过添加 user _ 前缀可以轻松解决此问题，但仍要牢记。


• SQL注入漏洞。如果我注册一个名为 lol的用户怎么办？ DROP TABLE帐户； -？查询参数是防止此类攻击的主要方法，不适用于表名。

• You can't search messages based on any field that isn't the username. With your current solution, how would you find all messages for a certain questionNumber?
• You can't join with the messages tables. You have to make two queries, one to find the table name and one to actually query the table, which requires two round-trips to the database server.
• Each user now has their own table schema. On an upgrade, you have to apply your schema migration to every messages table, and God help you if some of the tables are inconsistent with the rest.
• It's effectively impossible to have foreign keys pointing to your messages table. You can't specify the table that the foreign key column points to, because it won't be the same.
• You can have name conflicts with your current setup. What if someone registers with the username accounts? Admittedly, this is easy to fix by adding a user_ prefix, but still something to keep in mind.
• SQL injection vulnerabilities. What if I register a user named lol; DROP TABLE accounts; --? Query parameters, the primary way of preventing such attacks, don't work on table names.

我可以继续。

请合并所有表，并仔细阅读数据库规范化。

Please merge all of the tables, and read up on database normalization.

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