Many to many relationship or one to many with a separate pivot table?

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.

Solution

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

SET CONSTRAINTS ALL DEFERRED not working as expected
Verify and/or Recommend Re Database Design for an Internal Admin Chat System
Why should I create separate payment table for orders table for ecommerce database
How do I store a directory / hierarchy / tree structure in a database?
How can I model resource exchange processes?
Why would you want to put an index on a view?
SQL table design with foreign key
Use of SQL Schema
Execute deferred trigger only once per row in PostgreSQL
In ER diagram, is order and cart relationship are weak entity relationship?
Relational table design with same type in a many-to-many relationship
Where Constants should be stored in the database
What is suggested database schema for order/invoice information?
internal implementation of database queries
How can you represent inheritance in a database?
Database Design Without Foreign Keys
MySQL database design for image options relationships
Normalization of vehicle data
Should I always use navigation properties or use the .Where query?
Ensuring consistent common reference among two tables
Calculating and saving space in PostgreSQL
Should I create a surrogate key instead of a composite key?
Relational Data Model for Double-Entry Accounting
MySQL partitioning by week automatically
When should you use a one-to-one relationship with tables in your database?
Housing Society management system database structure
How do I simplify this model with a double reference?
To use a FK in a one-to-many relationship versus using a join table
Facing the problem of uploading the member ID while uploading the data to multiple tables through a single form/click
Difference between superkey and composite key