0

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.

user4157124
  • 2,809
  • 13
  • 27
  • 42
bennythemink
  • 5,096
  • 4
  • 36
  • 54

2 Answers2

0

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

NickW
  • 8,430
  • 2
  • 6
  • 19
  • Thanks Nick, excuse my ignorance but what is the difference between an intersection table and a pivot table? – bennythemink Jul 03 '23 at 08:55
  • 1
    A pivot table is the result of pivoting data i.e. pivoting rows to columns. An intersection table is used to model a m:m relationship – NickW Jul 03 '23 at 08:58
0

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.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • Thanks for the info Chris. That helps. I think NickW's answer suits my situation the most but your reply is appreciated and does provide a lot of insight. – bennythemink Jul 03 '23 at 09:15