Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

geope's avatar
Level 1

Three way pivot table

Let me start by saying that i am new at Laravel and I have been struggling to find the best design for creating a system that will be used to create Roles, map several Groups to each role and assign those Roles to Countries.

So essentially the database logic is:

  • Each country has many Roles
  • Each role can belong to many Countries
  • Each Role has many Groups
  • Each Group can belong to many Roles

For simplicity's sake, let's say that i have the Role of 'Accountant' which exists in the UK, US and France. I would like to associate this Accountant role with those 3 countries and then assign specific groups to the UK Accountant, specific groups to the US Accountant and specific groups to the France Accountant role. Some groups may be common in both countries Accountants. As i don't want to have repeating Role names i would prefer to keep the role generic e.g. Accountant instead of using something like UK_Accountant, US_Accountant etc. and just somehow associate the country/group/roles.

I played around and created a prototype using a 3-way pivot table but i think that there must be a better solution as this solution seems quite limiting and not very flexible. For example, at some point i just want to associate the Country with the Role whereas if i use the 3-way pivot table i also need to enter the Group in the DB row. This solution feels that it won't scale well when i try to implement additional functionality.

In your opinion what would be the 'cleaner' way to proceed with this to allow for easier data input and output from the DB? Ideally i'd like to stick to pure Eloquent but i think i'll need to use raw SQL.

My DB will be as follows

Group:
- id
- group_name
- group_type

Role:
- id
- role_name

Country:
- id
- country_name
0 likes
0 replies

Please or to participate in this conversation.