How to think about and organize a Many-to-Many bidirectional database

First, I am new to database management, so my terminology might be slightly incorrect.

I have data I believe would best fit into a many-to-many bidirectional database (I am using SQL with Java). I am trying to represent the relationship between employees and job positions. Each employee, identified by a unique id, can hold one or more positions, and each position can have any number of employees. The complicated part is that there is a priority ranking from both ends: for each employee, they will have an ordered list ranking of the positions they are capable of, and for each position, there will be an ordered list of prioritized employees.

For example, let’s say we have seven employees (ids 00-06) available for a given shift, and two positions, supervisor and cashier. One supervisor is needed, and between two and four cashiers.

• employee00 holds only the cashier position
• employee01 holds only the cashier position
• employee02 holds only the cashier position
• employee03 holds both the supervisor and cashier position, with supervisor being prioritized
• employee04 holds both the supervisor and cashier position, with supervisor being prioritized
• employee05 holds both the supervisor and cashier position, with cashier being prioritized
• employee06 holds only the supervisor position

There are five employees (00-05) who are qualified for cashier, and four employees (03-06) qualified for the supervisor position. Since there are fewer qualified supervisors than cashiers, and you need only one per shift, the scheduling software will assign the supervisor position first Typically, the system would rate the priority as employee06 > employee03 = employee04 > employee05, meaning that employee06 (who can only be a supervisor) would be assigned the position for that shift.

My system gets complicated because each position also has a priority ranking. So if the supervisor position’s priority ranking favors employee03 (perhaps they are just really good but are new enough that it is appropriate to also put them as a cashier), they would be scheduled even above employee06. The new system would then have the priority ranked as employee03 > employee06 > employee04 > employee05.

My gut instinct is to make a table for each position, with columns for employee id and relative priority within the position. An employee id could be in multiple tables, and the program could compile the tables by searching for that ID to get all positions the employee can fill. There would be a final table of employees listing their names, contact information, and other stuff that is not taken into account by the scheduler.

My question, as someone new to database design, is if this is a valid way of thinking about the problem.

>Solution :

I would suggest a bridge version.

As you see in the sample, you can easily get a supervisor and two cashiers and the priority is also build in.

The RAND() guaranties that not always are the same persons selected, that of course if you have enough employees at your hand.

Adding more information, like workdays for each employee, or how much they earn can refine the structure:

CREATE tABLE employee(id_emp int Primary Key, employee_name varchar(50))

INSERT INTO employee VALUES (1,'Emp0'),(2,'Emp1'),(3,'Emp2'),(4,'Emp3'),(5,'Emp4'),(6,'Emp5'),(7,'Emp6')

CREATE TABLE position (id_pos int PRIMARY KEY, position_name varchar(50))

INSERT INTO position VALUES (1,'cashier'), (2,'supervisor')

CREATE TABLE Pos_EMP(emp_id int, pos_id int, prio int)

INSERT INTO Pos_EMP VALUES (1,1,1),(2,1,1),(3,1,1),(4,1,2),(4,2,1),(5,1,2),(5,2,1),(6,1,1),(6,2,2),(7,2,1)

WITH FIRST_Supervisor as (SELECT id_emp,  employee_name, position_name 
  FROM employee e INNER JOIN Pos_EMP pe ON e.id_emp = pe.emp_id
INNER JOIN position p ON p.id_pos = pe.pos_id
WHERE pos_id = 2 ORDER BY prio, RAND() LIMIT 1)
  SELECT employee_name, position_name  FROM FIRST_Supervisor
  UNION ALL 
(SELECT  employee_name, position_name 
  FROM employee e INNER JOIN Pos_EMP pe ON e.id_emp = pe.emp_id
INNER JOIN position p ON p.id_pos = pe.pos_id
WHERE pos_id = 1 AND emp_id NOT IN ( SELECT id_emp FROM FIRST_Supervisor)  ORDER BY prio, RAND() LIMIT 2)

fiddle