PHP MySQL日志的其他提交

Question

I have an online Test Taking System where once the user submits the test form it adds a row to the table like so;

Employee ID | Employee Name | Date Test 1 Taken

Employees can retake tests so I want to replace the 'Date Test 1 Taken' with the date of the most recent submission, however, I also want to log all the previous submissions.

So basically I'd want a second table with 3 previous submissions like so; Employee ID | Previous Submission 1 | Previous Submission 2 | Previous Submission 3

Is it possible to essentially do "This employee retook this test on a different date, so take the current date in 'Date Test 1 Taken', move it to the other table under 'Previous Submission 1' and place the current date in 'Date Test 1 Taken'? Is it extremely complicated, if at all possible..?

Answer 1

I would suggest a design improvement. It seems your goal is to keep a running record of each employee and test type. Your table should be something like:

EmployeeTest table: EmployeeID, EmployeeName, TestNum, DateTaken, Score

You insert a row to the EmployeeTest table for every test taken. You can then write queries like:

select EmployeeID, EmployeeName, TestNum, DateTaken, Score from EmployeeTest order by DateTaken

To see all test for an employee in date order. Or:

select EmployeeName, TestNum, max(DateTaken), Score from EmployeeTest where EmployeeID = 'joe' and TestNum = 1

To get the latest date and score for employee 'joe' for test #1

Of course the EmployeeName should be broken out into an Employee table (EmployeeID, EmployeeName), but I didn't want to muddy the issue with joins at this point.

Have fun...