0

I have a Laravel project in which there is likely to be multiple many to many relationships but I feel like I'm going around in circles in terms of the correct use of pivots tables.

The entities are as follows:

  • Centre: an office that is going to open soon and has information such as name, location, postcode, opening date
  • Task: a task that must be completed before a centre can open, it has information such as name, department and centre type
  • Department: a department represents a key area such as IT, Marketing, Sales etc. Each task would have a reference to a department.
  • User: a user is a user of the system, they belong to a department and they can complete tasks.

Considerations:

  • A department has users, so users would have a department_id
  • A task has a department so would also have a department_id
  • A centre has departments so a pivot called something like centre_department with centre_id and department_id
  • A user has tasks assigned to them but only tasks that belong to a centre

The bottom statement is where I'm going in circles because:

  • A Task is to be assigned to a Centre which is fine as you could make a table like centre_task with centre_id and task id
  • A user has tasks assigned to them, however, is it okay to have a task_user table that has user_id and a centre_task_id, effectively a pivot table that uses another pivot table?

This is because a user can't be directly assigned to a task, they can only be assigned to a task within a centre as the tasks have a many to many relationship with centres.

If I were to assign a user just using task id, they would be assigned to that task in every related centre.

So, again is it okay to have a pivot table that uses another pivot table, or does this display an issue with the structure I have suggested?

Intended flow

  • A user creates a task, assigns it a department and a type so that this task is only relevant to a specific department and type of centre.
  • A user can assign a task within a centre to a user who is in the department that's relevant to the department of the task
  • The task in the centre would then have an assignee and a deadline

In theory I'd end up with a pivot table that has the following:

  • user_id
  • centre_task_id
  • deadline
  • date_completed

Essentially I just feel like I'm connecting too many pieces?

Jesse Luke Orange
  • 1,949
  • 3
  • 29
  • 71
  • I have an answer to a similar question, hopefully this helps! [JOIN queries vs multiple queries](https://stackoverflow.com/a/36144454/2430549) – HoldOffHunger Mar 15 '21 at 16:42

1 Answers1

0

Laravel does the linking for you, simply use (in this case) morphToMany() & morphedByMany() in your Models to establish a relation between two tables. There is a very good Laracast and also a very good Documentation. I have linked this below:

Laracast Video very nice Explaination for Many-To-Many Relations

Laravel Documentation for Eloquent ORM In General

Daniel Juric
  • 128
  • 1
  • 10