英文:
Many to many relationship or one to many with a separate pivot table?
问题
表格 clients 可以拥有多个用户。99.9% 的用户仅属于一个客户。但有一种特殊类型的用户（审计员）可以“属于”多个客户。
我应该将其建模为 clients 和 users 表格之间的多对多关系，通过一个中间表，还是建立一个一对多关系，并有一个单独的中间表来跟踪仅适用于这种特殊审计员情况的多对多关系？
我正在使用 PlanetScale（MySQL 数据库）和 Prisma 作为我的 ORM。
英文:
Table clients can have many users. 99.9% of users belong to only one client. But there is a special type of user (auditors) that can “belong” to multiple clients.
Should I model this as a many to many relationship via a pivot table between the clients and users tables, or a one to many relationship and have a separate pivot table that tracks the many to many relationship just for this special auditor case?
I'm using PlanetScale (MySQL database) and Prisma as my ORM.
答案1
得分: 0
有一个1：m的关系，然后有一个交集（不是枢轴）表来建模审计师关系。
假设审计师存在于用户表中，可能无法仅通过纯建模/SQL方法限制仅将审计师链接到m：m表 - 您可能需要在应用程序逻辑中实现这一点。
英文:
Have a 1:m relationship and then an intersection (not pivot) table to model the auditor relationship.
Assuming that auditors exist in the user table, it may not be possible to limit just auditors being linked to the m:m table - using a pure modelling/SQL approach. You may need to implement this in your application logic
答案2
得分: 0
每个用户都有一个客户。
一个客户可以有一个审计员。如果审计员之间有多对多关系，你还需要在审计员和客户之间创建一个中间表。
所以，类似于以下内容：
create table users (
...
client_id int
);
create table clients (
....
auditor_id int
);
create table auditor (
...
);
请注意，上面我没有定义主键和外键关系，但它们应该很明显，你可以自行添加。
如果审计员有单独的用户，你可能还需要让审计员也成为客户（在审计员表中添加一个 client_id，并通过某种方式进行延迟处理，可能是写入为 null，然后稍后更新，不确定 MySQL 是否支持延迟外键约束）。
希望这能帮助你入门。
英文:
It seems to me that your model is fairly simple:
Each user has one client.
A client may have one auditor. If you have m:m for auditors you also need a pivot table between auditors and clients.
So something like:
create table users (
...
client_id int
);
create table clients (
....
auditor_id int
);
create table auditor (
...
);
Npte that I am not defining pkey/fkey relationships above but they should be pretty obvious and you can do that.
If the auditors have separate users, you may also need to have an auditor also be a client (so have a client_id in the auditor table and some way of doing deferred handling there, possibly by writing as a null and then updating after, not sure if MySQL supports deferred fkey constraints).
Hope that gets you started.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论