I am doing an exercise of a Task allocator for 2 workers. Each worker must be assigned to an area. It works with mysql 5
My Task table has: id (autoincremental), area, task and worker. For example, it should look like this, each worker in an area:
Area | Task | Employee
HR | personal assistance | worker1
HR | carry out settlements | worker1
Accountant | make declarations |Worker2
Management| plan agenda | worker1
Logistics| plan orders | Worker2
Logistics| contract suppliers| Worker2
I have been doing this, I have declared a variable to generate a ranking grouped by area but the result assigns everything to Worker1, please, any suggestions, I can't find a way to assign a Worker by area??
$sql = "SELECT id,area, count(*),@rn :=d( @rn+1 ) AS rank,trabajador FROM dbempresa.tareas, (SELECT @rn:=0) a group BY area";
$result = mysqli_query($con,$sql);
$cantidad = $result -> num_rows;
while ($row = $result -> fetch_array()){
for ($i=1;$i<$cantidad;$i=$i+2){
$consulta="UPDATE dbempresa.tareas SET trabajador=concat('Trabajador','1')where @rn=$i";
$resulta = mysqli_query($con,$consulta);
$consulta="UPDATE dbempresa.tareas SET trabajador=concat('Trabajador','2') where @rn=('$i'+1)";
$resulta = mysqli_query($con,$consulta);}}
I understand that parts of a situation like this:
In which you must assign a series of tasks to a series of workers, keeping each one of them in a specific work area.
To do this, I suggest you normalize your model a bit, obtaining something like this:
For what you can use these instructions:
Since each worker must be assigned to an area, I would directly relate these two entities with:
Getting the diagram:
Note that, as your example indicates, I assume that in each area there will only be one worker and the same worker can work in different areas.
Therefore, it only remains to make the assignments of areas to each worker using the module function:
For this to work, there must be no gaps in the autoincrementals, for which I suggest you use another field that you take care of renumbering before making the assignment and thus avoid surprises.
With this alternative field to the one
id
created, we can now assign areas without errors:One more thing, since the worker depends on the area, it would be convenient to unlink the worker from the task, since the assigned worker can be obtained through his area, avoiding generating that redundancy loop:
Leaving the database diagram like this:
And arriving at the result:
With the query:
I hope you have found it useful, especially for the part in which I start from a model that does not fit reality to end up arriving at the one that should have been the starting one. Sometimes, investing a while doing the ER model is worth it to not go around so much. Leave me any questions in the comments.