多租户应用程序的数据库设计

for a multi tenant app, we have the following database design:

对于多租户应用，我们有以下数据库设计：

which is based on shared database approach. Since we are identifying the tenants using company id (each company has a different set of employees and their tasks and so on), my question is

这是基于共享数据库的方法。由于我们使用公司ID识别租户（每家公司都有不同的员工及其任务等），我的问题是

Do we need a companyId key in the Task table also so that every record of task can be clearly identified using the companyId OR we should always use a Join?

我们是否还需要Task表中的companyId键，以便使用companyId可以清楚地识别每个任务记录，或者我们应该始终使用Join？

because if we use the companyId in Task that would not be a properly normalized database as the Task is would relate to a company and an employee which is also related to the company.

因为如果我们在Task中使用companyId而不是正确规范化的数据库，因为Task将与公司和与公司相关的员工相关。

1 个解决方案

#1

It is a matter of opinion. My take is to make companyId as a part of primary key and hence a mandatory field in every table.

这是一个意见问题。我的看法是将companyId作为主键的一部分，因此在每个表中都是必填字段。

In a multi tenant application, we should ensure that data does not get added in the table without a company code. Without making it a part of primary key or a non null field, it is upto program logic to ensure that. In my opinion, DB should also ensure that. Second issue is with the table task id. It is possible for 2 companies to have same employee id's and same task ids. DB should not restrict that.

在多租户应用程序中，我们应该确保在没有公司代码的情况下不会在表中添加数据。如果不将它作为主键或非空字段的一部分，则可以通过程序逻辑来确保它。在我看来，DB也应该确保这一点。第二个问题是表任务ID。 2家公司可能拥有相同的员工ID和相同的任务ID。 DB不应该限制它。