Adding multiple datasets to single table

Question

I have a table1 in DB1 which is similar to the following

+----+------+----------+--------+----------+----------+-------------+---------+--------+----------+----------+---------+---------+--------+----------+----------+---------------+---------+--------+----------+----------+--------+
| id | name | location | rname1 | rscore11 | rscore21 |   rdesc1    | enable2 | rname2 | rscore12 | rscore22 | rdesc2  | enable3 | rname3 | rscore13 | rscore23 |    rdesc3     | enable4 | rname4 | rscore14 | rscore24 | rdesc4 |
+----+------+----------+--------+----------+----------+-------------+---------+--------+----------+----------+---------+---------+--------+----------+----------+---------------+---------+--------+----------+----------+--------+
|  1 | John | locA     | Dale   |        5 |        4 | description | Y       | Bob    |        2 |        3 | Another | Y       | Bill   |        5 |        2 | text data     | Y       | Jeff   |        4 |        2 |        |
|  2 | Paul | locB     | John   |        4 |        2 | description | Y       | Vinc   |        4 |        5 |         | Y       | Phil   |        3 |        4 | detailed data | N       |        |          |          |        |
|  3 | Vinc | locA     | Chou   |        4 |        3 | description | Y       | Dilan  |        1 |        5 | review  | N       |        |          |          |               |         |        |          |          |        |
+----+------+----------+--------+----------+----------+-------------+---------+--------+----------+----------+---------+---------+--------+----------+----------+---------------+---------+--------+----------+----------+--------+

Those data is the supervisor evaluations of employees.

• One employee can review more than 1 supervisor with the same evaluation form
• If an employee is not using a secondary evaluation slot, subsequent evaluation slots will not be available to him. (One evaluation slot includes the criteria of a single supervisor)
• Evaluation 1 is mandatory as a person who is attempting the evaluations shall evaluate at least 1 supervisor

The data is gathered and stored by a separate script. For our own review purposes, I am processing data to obtain the following results in table2 of DB2;

+----+--------+------+----------+-------+---------+---------+---------------+
| id | app_id | name | location | rname | rscore1 | rscore2 |     rdesc     |
+----+--------+------+----------+-------+---------+---------+---------------+
|  1 |      1 | John | locA     | Dale  |       5 |       4 | description   |
|  2 |      1 | John | locA     | Bob   |       2 |       3 | Another       |
|  3 |      1 | John | locA     | Bill  |       5 |       2 | text data     |
|  4 |      1 | John | locA     | Jeff  |       4 |       2 |               |
|  5 |      2 | Paul | locB     | John  |       4 |       2 | description   |
|  6 |      2 | Paul | locB     | Vinc  |       4 |       5 |               |
|  7 |      2 | Paul | locB     | Phil  |       3 |       4 | detailed data |
|  8 |      3 | Vinc | locA     | Chou  |       4 |       3 | description   |
|  9 |      3 | Vinc | locA     | Dilan |       1 |       5 | review        |
| 10 |      3 | Vinc | locA     | Chou  |       4 |       3 | description   |
+----+--------+------+----------+-------+---------+---------+---------------+

I am currently using the following MySQL query to extract data from table1 and to insert the same in table2

$geteval = mysqli_query($db1,"SELECT * FROM table1 WHERE location<>''");
while ($y = mysqli_fetch_array($geteval)) {

$addeval = mysqli_query($db2,"INSERT INTO `table2` (`app_id`, `name`, `location`, `rname`, `rscore1`, `rscore2`, `rdesc`)
VALUES ('{$y["id"]}', '{$y["name"]}', '{$y["location"]}', '{$y["rname1"]}', '{$y["rscore11"]}', '{$y["rscore21"]}', '{$y["rdesc1"]}')");

if ($y["enable2"] == "Y") {
$addeval = mysqli_query($db2,"INSERT INTO `table2` (`app_id`, `name`, `location`, `rname`, `rscore1`, `rscore2`, `rdesc`)
VALUES ('{$y["id"]}', '{$y["name"]}', '{$y["location"]}', '{$y["rname2"]}', '{$y["rscore12"]}', '{$y["rscore22"]}', '{$y["rdesc2"]}')");
}

if ($y["enable3"] == "Y") {
$addeval = mysqli_query($db2,"INSERT INTO `table2` (`app_id`, `name`, `location`, `rname`, `rscore1`, `rscore2`, `rdesc`)
VALUES ('{$y["id"]}', '{$y["name"]}', '{$y["location"]}', '{$y["rname3"]}', '{$y["rscore13"]}', '{$y["rscore23"]}', '{$y["rdesc3"]}')");
}
}

This continues for all evaluation slots. The above code executes fine and update the databases without errors. But I have a concern that I maybe wasting server resources with the said code. Is there anyway I could improve the efficiency of the insertion process?

Answer 1

the correct solution here is to properly normalize the data by extracting the evaluation slots into a separate table. That way you get to the "third normal form".

This makes it significantly easier to change what can be evaluated and fixes a ton of problems with SQL performance.
Consider the following table definitions:

Evaluation (*Id*, *SequenceNumber*, SupervisorId, Score1, Score2, Description)

This basically already matches your desired result scheme. There's a consideration to be made for the "history of employment", where you should denormalize the location out of the employee-record.

If you decide against it, the following Select statement already accomplishes what you want:

SELECT Rank(*), Id, sup.Name, sup.Location, sup.RName, Score1, Score2, Description
FROM Evaluation JOIN Employee AS sup ON sup.Id = SupervisorId;

Now the only thing that's left to not waste server resources is to refactor the code of the original script to use the improved table structure.