Discussions

Expand all | Collapse all

Many to Many Relationship

  • 1.  Many to Many Relationship

    Posted 04-29-2020 13:49
    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
    ------------------------------


  • 2.  RE: Many to Many Relationship

    Posted 04-30-2020 06:39
    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
    ------------------------------



  • 3.  RE: Many to Many Relationship

    Posted 24 days 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
    ------------------------------



  • 4.  RE: Many to Many Relationship

    Posted 24 days 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
    ------------------------------