I am developing a SaaS web application for School Management. What I need help is this:
The whole application is purposed for the current year that the School is in. However, some data and statistics should be able to be accessed from passed years.
For example: It is extremely important to keep old students contact details or grades etc. However I don't think its a good idea to keep all the old and current students on the same table, because I will have to manually use "WHERE" clauses everywhere (Grades, evaluations, comments etc.) and that might be a bit "messy". So, another approach would be to use a different database perhaps for each year. Keep in mind i intend to use a single-tenant approach.
Any ideas on these two approaches or perhaps something else?
student
table. There should be additional tables: enrolments. courses, qualifications. You only need to store the academic year in the enrolments if you wanted to. (I used to work in a college). Now you join your tables but where enrolment_year ='15/16' etc. Or have academic years in a seperate tables andwhere enrolment_year = 21
. Then you can search the enrolments, to get students, courses, and grades for the academic year.