Web 应用程序的基本数据库架构
我正在尝试构建一个基于网络的任务管理系统。我希望不同的组织能够访问不同的任务列表。我可以想象有两种方法来设置数据库架构：
- 所有组织的所有任务都存储在一个表中。与组织 X 关联的任务记录具有为 X 的 ID 号存储的外键。
- 每个组织都会收到自己的任务表。该表有一个前缀来标识它属于该组织。单独的表存储组织和表前缀之间的关联。
1. 与 2. 有哪些优点和缺点？哪一个扩展性更好？另外，我正在考虑允许用户关闭和打开任务的某些属性，例如，跟踪您在某项任务上工作了多长时间的能力。我想象我最好通过在任务表中添加或删除字段来做到这一点。但是，由于不同的组织会有不同的配置设置，因此 2. 是否更适合此目的？
I'm trying to build a web-based task management system. I want different organizations to be able to access different lists of tasks. There are two ways I can imagine going about setting up the database architecture:
- All the tasks for all the organizations are stored in a single table. Task records associated with organization X have a foreign key stored for X's ID number.
- Each organization receives its own task table. The table has a prefix to identify it as belonging to that organization. A separate table stores associations between organizations and table prefixes.
What are the advantages and disadvantages of 1. vs. 2.? Which one scales better? Also, I'm considering allowing users to turn off and on certain attributes of tasks, e.g., the ability to keep track of how much time you've been working on a task. I was imagining I could best do this by adding or removing fields from the task table. Since different organizations will have different configuration settings, though, would 2. be better suited for this purpose?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
为每个组织提供自己的任务表听起来并不是一个好主意。当新组织加入时，这将需要更改物理数据库，并且需要更改潜在的代码以从新表中读取（尽管您可以动态更改前缀）。如果您想将来更改结构（例如添加新字段），您还会有更多的开销。随着时间的推移，这将限制您重新设计和改进的能力。此外，将负载分散到多个表上意味着您需要保持索引和其他类似内容同步。由于涉及多个表，备份变得更加麻烦。
我认为最好的解决方案是使这个数据驱动，其结构类似于下面的结构：
这样您就可以动态更改活动任务，设置新的组织等。在应用程序中使用缓存来提高性能。
It doesn't sound a good idea to give each organization its own task table. This would require physical database changes when a new organization comes onboard and potential code changes to read from the new tables (although you could change the prefix dynamically). You also have more overhead if you want to change the structure in the future e.g. adding new fields. This will limit your ability to redesign and improve as time goes on. Also spreading the load over multiple tables means you need to keep indexes and other stuff like that in sync. And backing up is made more cumbersome as there are multiple tables involved.
I would have thought the best solution is to make this Data Driven, with a structure similar to that below:
This way you can dynamically change the active tasks, set up new organisations, etc. Use Caching in your app to improve performance.
其搜索术语是“多租户数据库”或“多租户架构”。
此 StackOverflow 问题<中有一个简短的概述和指向有用文章的链接/a>.
The search term for this is "multitenant database" or "multitenant architecture".
There's a brief overview and a link to a useful article from this StackOverflow question.
正如您所描述的，我做了这件事，为每个“组织”提供了自己的表格。当新组首次访问表支持的功能时，表将作为基表的空副本即时创建。因此，仅需要“维护”基础，如果它发生更改，唯一的开销是（记住）删除婴儿表，以便可以根据更改后的妈妈表重新创建它们。
在这种情况下它工作得很好。
I did this thing where I gave each "organization" its own table, as you describe. When a new group first accesses the function supported by the table, the table is created on the fly as an empty copy of a base table. Thus only the base need be "maintained," and if it is changed, the only overhead is to (remember to) delete the baby tables so that they can be recreated according to the changed mamma table.
It works just fine in this instance.