I plan to design a high school db, with stud's info, teachers' info, classes, and so forth.
here comes my question:
we all know sch has many classes. In my case, the sch has classes such as
1A, 1B, 1C, 2A, 2B, 2C, 3A and so forth.
The name or id (example: 4A, 4B, 3C, 3A) of each class remains the same throughout the years but the students are changing, as students in grade 5 will eventually move up to grade 6 in the following year. For instance, all students in 5A move up to class 6A. therefore in this new academic year, names (table) in class 5A and 6A will be replaced by the names of other students.
How am I gonna retain the records in previous years, in case users wanna retrieve the names or any other data of a class in a particular year?
how am i gonna design the db to handle this situation?? i'm new to be db design, running out of idea.
I know it's long, and thanks a lot for reading it. any help/suggestion will be greatly appreciated.
Include the academic year as a column in your design This way you will also be able to handle the change of students and of class names from year to year (I know you say it never, ever happens - I am old & bitter enough to tell you that you never take for granted that nothing "never, ever changes"!).
thanks for the input pootle flump.
u mentioned abt including the academic year as a column. do u mean i just add it to the STUDENT_INFO table? so far my STUDENT_INFO table has these fields:
Name, DOB, Address, Sex.
i've thought of adding a 'Class' field to the above table, but as i've said, students are having different classes every year...
or can u give me more details about how i should go about it??