在 SQL 中管理多个帐户和用户以及与帐户关联的资产
我正在构建一个可以拥有来自多个帐户的多个用户的应用程序。例如，帐户可以是公司 ABC。用户 X、Y 和 Z 是该帐户的成员。每个帐户都应该有自己单独的实例，以便如果公司 ABC 创建新的数据库项目，则该项目只能由公司 ABC 可见和管理。我的问题是：我是否必须对数据库中每个表中的帐户进行显式外键引用？例如：
表 - 帐户
ACCOUNT_ID | ACCOUNT_NAME
1234 | Company ABC
表 - 页面
PAGE_ID | PAGE_TITLE | ACCOUNT_ID
987 | My Page | 1234
表 - 资产
ASSET_ID | ASSET_TITLE | ACCOUNT_ID
4443 | My Asset | 1234
表 - 组
GROUP_ID | GROUP_NAME | ACCOUNT_ID
8888 | Admins | 1234
等？
出于某种原因，这对我来说似乎是错误的，我觉得有一种我没有想到的更好的方法。我有近 75 张桌子需要执行此操作。这是对的吗？
I am building an application that can have multiple users from multiple accounts. For example, an account could be Company ABC. Users X, Y, and Z are members of this account. Each account should have its own separate instance so that if Company ABC creates a new DB item, it should only be visible and manageable by Company ABC. My question is this: do I have to make an explicit foreign key reference to the account in every single table in my DB? For example:
TABLE - ACCOUNTS
ACCOUNT_ID | ACCOUNT_NAME
1234 | Company ABC
TABLE - PAGES
PAGE_ID | PAGE_TITLE | ACCOUNT_ID
987 | My Page | 1234
TABLE - ASSETS
ASSET_ID | ASSET_TITLE | ACCOUNT_ID
4443 | My Asset | 1234
TABLE - GROUPS
GROUP_ID | GROUP_NAME | ACCOUNT_ID
8888 | Admins | 1234
etc?
This seems wrong to me for some reason and I feel like there is a better way that I'm not thinking of. I have nearly 75 tables that I would need to do this for. Is this right?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我不得不处理这种情况，并且您可能必须在许多（但不一定是全部）表中包含 ACCOUNT_ID 列。另一种方法是为每个帐户建立单独的数据库。这可能会导致维护问题，因为您必须确保 DDL 和 DML 的所有更改都得到普遍应用。它还可能导致性能问题。将列应用于每个表确实（稍微）使查询连接和数据所需的视图变得复杂，但就性能和空间而言，连接通常成本较低（或没有）。单独数据库的一个优点是它可能是一种更安全的解决方案——将每个帐户与所有其他帐户隔离开来。
我建议并非所有的表都需要帐户列。对此的需求取决于访问路径。 - 例如，我的表中表达了子/超类型关系。每个子类型和每个超类型都有自己的表。对所有子类型的访问只能通过超类型进行，因此超类型需要引用 ACCOUNTS，但子类型不需要。
编辑：
我的问题 以及关于此类设计问题的答案和评论得出了我的上述结论。
I have had to deal with this situation, and it is likely that you will have to include the ACCOUNT_ID column on many (though not necessarily all) your tables. An alternative is to have separate databases for each account. This can lead to maintenance problems, as you have to ensure that all changes to DDL and DML are universally applied. It also potentially leads to performance problems. Applying the column to each table does (slightly) complicate the queries joins, and views needed on the data, but joins are generally low (or no) cost in terms of performance and space. The one advantage of separate databases is that it is likely to be a more secure solution - ring-fencing each account from all others.
I suggested that not all your tables would need the account column. The need for this would depend on access paths. - For example, I have sub/super type relationships expressed in my tables. Each sub type and each super type has its own table. Access to all sub types is only through the super type, so the super type would need reference to ACCOUNTS, but the sub types would not.
EDIT:
My question and the answers and comments on it, concerning this type of design question led to my above conclusion.