We can already see a couple of flaws in this setup, like, what if the admin makes the 2nd grade first, so level.before: 1st grade and level.after: 3rd grade were not made yet.
Maybe a different set-up, that records level changes.
A table for students…
A table for grades: grade_id, grade_name
And a grade record table: record_id, student_id, grade_id, pass_date
The only issue I see is the “next grade”, as this has not happened yet, but presumably, there is some sort of order or hierarchy to the grades to say which is next.
grade_rank (int, UNIQUE, PRIMARY, but *NOT* autoincrement), grade_name (varchar)
Admin creates ranks, and can shift them up-and-down the rank scale. (Note: What you actually swap is the names, not the ranks).
The student can then store either simply an int, or if you’re following sam’s method, an int for the current rank and a table of history for the student.
The advancement/demotion is then simple mathematics - +1 or -1.
I was trying to keep the ranking-up-or-down query simple.
It can be done that way, but then your “graduation” code has to do a MIN of the ranks above the current rank…