Leveling up

Databases
1

I’m working on a system that automatically moves the student’s level up if they have a passing grade.

I have two users; admin and students. Admin adds the year levels.

I have this table

   Level
 ---------
|before   |
|current  |
|after    | 
 ---------
Example Data:
previous: 1st grade
current: 2nd grade
next: 3rd grade

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.

What can I do with this?

2

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.

3

Yes, I’m actually following the Educational Stage, from Kindergarten to University/College/Tertiary level.

4

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.

5

So grade_rank is not autoincrement, how does it accepts data? So the data would be like this?

grade_rank: ?
grade_name: 1st Grade

grade_rank: ?
grade_name: 2nd Grade

I’m sorry if I’m wrong.

6

adding a new rank would be to insert name, and MAX(grade_rank)+1.

switching two ranks would be an inequality join shift,

and deleting a rank would be deletion and then -1’ing all ranks above the deleted rank. (this wouldn’t be useful in an autoincrementing field)

7

how about not deleting, and simply relying on their relative sequence

original ranks
1
2
3
4
5

delete 3

new ranks
1
2
4
5

insert a new rank in between 1 and 2

new ranks
1
1.5
2
4
5
8

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…

9

Do these ranks need to be created in order? Because admin might mess up and create 2nd grade before 1st grade.

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.