class
id INT UNSIGNED
grade TINYINT
class CHAR(1)
start_year DATE
-- matches id of a teacher (assuming each class has one teacher responsible for the class)
-- even if the class may have more than one teacher
mentor INT UNSIGNED
FOREIGN KEY (mentor) REFERENCES teacher(id)
student
id INT UNSIGNED
name
family_name
born
phone
cell
teacher
more or less same as student
student_class
class_id INT UNSIGNED
student_id INT UNSIGNED
start_year CHAR(4)
With this approach, you'd have new entries in class every year for each grade and class. Thus, two year in a row you will have 5A and 5A, but you may not have 5F every year, since the number of classes might change with the number of students.
You will also have one entry per student and year in the student_class table, which is called a link table among other things. The use of a link table is to allow an M:N (many to many) relation.
To find out which students are in a specific class during this year, you'd
SELECT name, famil_name
FROM student AS s
INNER JOIN student_class AS sc ON sc.student_id = s.id AND start_year = '2012'
INNER JOIN class AS c ON c.id = sc.class_id AND c.start_year = sc.start_year
WHERE grade=5 AND class='A'
Next year you'd insert a new entry in student_class for each student matching one of the newly inserted classed.
This approach lets you go back in time and see what teacher was responsible for each class (called mentor in the table) every year. If you don't need that information, and only wish to keep track of the current year, you can simplify things a bit
class
same as above, but drop start_year
Now, you will not have one entry per class and year. You will keep the same entries all the time, possibly adding a new entry if you ever have more classes for a grade than you had before. E.g. adding grade 5, class 'G' if needs be, then grade 5 class 'H' when that's needed. You will however need to update the mentor value to reflect on a new teacher taking charge of the class (if that changes).
Since you don't keep historical track anymore, you will not need the student_class table either. At any given time, each student is only in one grade and class, which gives you a 1:N relation (one to many). As such
student
As before, but add
class_id
With this scenario, you simply update the class_id of each student every year to match whatever class they are placed in.
