CODEWITHSUNDEEP
mysqldatabase
user1730452
user1730452

Reputation: 155

MySQL many-to-many table relationship with three entities

Three record tables:

accounts, users and roles

Users can belong to multiple accounts with different roles.

Currently, I have a join table:

accounts_users_roles (With three columns for FK relationships to each tables PK.)

Primary key in this table is (accounts.id,users.id,roles.id)

Is there a more efficient way to establish these relationships?

EDIT: Roles and users may be shared by accounts. Accounts assign user roles.

Upvotes: 2

Views: 880

Answers (1)

Schwern
Schwern

Reputation: 164669

From the comments, I understand the business rules to be that a user has many roles for many accounts. Joe is the manager for the ConHugeCo account, and also does customer service for EvilInc. Kathy is president of the Hair Club and is also a client.

Yes, a single table is appropriate.

account     user     role
ConHugeCo   Joe      Manager
EvilInc     Joe      CustomerService
HairClub    Kathy    President
HairClub    Kathy    Client

create table accounts_roles_users (
  account_id integer not null references accounts(id) on delete cascade,
  role_id integer not null references roles(id) on delete cascade,
  user_id integer not null references users(id) on delete cascade,

  unique(account_id, role_id, user_id)
);

on delete cascade ensures when a user or account or role is deleted, their join table entry is deleted. And we avoid duplicate entries by making the user/account/role combination unique.

You may wish to allow the user_id to be null to indicate an unfilled role.

Upvotes: 1

Related Questions