Forum Discussion

KarimGawish's avatar
KarimGawish
Qrew Member
5 years ago

Many to Many Relationship

Hello All,
I have a table with lists of tasks that we need to work on daily and another Schedule table that has all employees and their availability dates. The usual situation is that 1 employee can finish several tasks per day, but some tasks require 2 employees to work on the same task.
So right now the relationship is that 1-day schedule has many tasks (Diagram 1 attached). With the date field being the key field in the schedule and the due date on the tasks table is the reference field.
How can I have this relationship without creating yet another table (Diagram 2 attached)?
Or what is the least messy way to achieve this.

------------------------------
Karim Gawish
------------------------------
Relationships
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    5 years ago
    Before you go any further I would look at that key field.  I do not think the date is going to be unique for an identifier.  If you have two sets of scheduled tasks on the same day it is going to create an issue with that key field.

    It sounds like you need a task assignment table which is a many to many (join) relationship between your tasks and your assignees (whatever you are calling them, maybe resources or employees, etc.)

    That allows the multiple assignments.

    I personally, in most business cases, do not like multiple assignees in tasks as it could lead to "I thought the other person had it"  But that is just me and some people on my team dont like that.  But since I develop QB for our team I get the last say :)

    ------------------------------
    Ivan Weiss
    ------------------------------
    • KarimGawish's avatar
      KarimGawish
      Qrew Member
      5 years ago
      Hi Ivan, I already have that as shown in the diagram it's called "Schedule" table, it has a many to one relationship to "Employees"

      ------------------------------
      Karim Gawish
      ------------------------------
      • IvanWeiss's avatar
        IvanWeiss
        Qrew Captain
        5 years ago
        I am not quite understanding exactly what you are trying to achieve with the schedule table.  What is that table doing for you?  Why not just have the tasks and employees tables with a join table in the middle?  That in essence would be your schedule table but the arrow to employees flipped the other way.  I am not following the use case of that schedule table.

        ------------------------------
        Ivan Weiss
        ------------------------------