MySQL many to many relationship structure (a can have many b's but b can also have many a's)

Question

Looking for the best way to go about this.

Basically its a setup for workers to see what jobs they have for a day.
Many workers can have the same job and multiple jobs for each worker are possible, each job can have multiple workers but not necessarily the same "bunch" of jobs.

Example:
worker a could have job 1 and 2 and
worker b could have job 1 and 3.

What is the best or a typical example of connecting this relation in a database?

My user table has ids and usernames I can reference but I am attempting to get away from some sort of list stored in the database which so far is my only idea, worker a has jobs (1,2,9).

Answer 1

The standard way to handle many-to-many relationships is to have an extra table.

workers_jobs:

| id   | worker_id | job_id |
-----------------------------
| 1    | 1         | 1      | 
| 2    | 1         | 2      |
| 3    | 2         | 1      |
| 4    | 2         | 3      |

This way you can select all jobs for a particular worker, and all workers for a particular job from this worker_jobs table.

For example:

SELECT * FROM workers 
    INNER JOIN workers_jobs ON workers.id = workers_jobs.worker_id 
    INNER JOIN jobs ON on workers_jobs.job_id = jobs.id

Answer 2

The common way to achieve this is by using an extra table which holds the relationship between workers and jobs. Supposing this table is named workers_jobs will have (at least) columns worker_id and job_id. If a worker with id 15 has jobs with ids 1,3,5 then you ll create three rows

   worker_id | job_id
        15   |    1
        15   |    3
        15   |    5

For the reverse relation if a job with id 2 belongs to workers with ids 2,5,6,8 your workers_jobs table will be

    worker_id | job_id
          2   |    2
          5   |    2
          6   |    2
          8   |    2

Storing comma separated values is not a good idea and an excellent explanation is provided here