0

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?

5
  • are you familiar with schemas ? Apr 27, 2015 at 10:24
  • yep, I have a good basic understanding Apr 27, 2015 at 10:26
  • i dont know if its good idea, but why not name schema as year then every year create new default schema. queries would be like something like (select o.*, n.* from 2015.students o left join 2016.students n where o.name LIKE n.name )... so basicly you would just create new database for each year (but in practise it would new schema in database) Apr 27, 2015 at 10:29
  • Your design should be changed. A students grades shouldn't be in the 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 and where enrolment_year = 21. Then you can search the enrolments, to get students, courses, and grades for the academic year. Apr 27, 2015 at 12:24
  • I am sorry maybe I did not explain it correctly. Of course there are separate tables for grades, enrollments, grades etc. I am not sure if I understand your suggestion correctly. Should I be joining all the tables with an enrolment year table? Apr 27, 2015 at 12:57

3 Answers 3

1

How large do you expect the data to grow? No harm in keeping older data in the same table. You could plan to use partitioning and implement date range partitions (per year). Using WHERE statements is completely natural in SQL so don't consider this as "messy" at all.

3
  • The messy part is that I will be selecting "where" in all the tables for 90 or 95% of the time, while ths could be avoided with separate databases for each year. I am wondering which is the most efficient way. Apr 27, 2015 at 13:00
  • What's the issue with using where? Explain how you envisage this working? All access to the table will use every row stored (SELECT * FROM table)???
    – eroomydna
    Apr 27, 2015 at 13:55
  • 95% of the time, I need to use current data. The problem is not the "where "itself. The problem is that I will have to add "YEAR" fields to almost all my data, and that seems wasteful. Apr 27, 2015 at 14:23
0

If it bothers you to include WHERE AcademicYear = '2015/16' very often, you could instead create a view which includes this filter:

CREATE VIEW CurrentEnrollment AS
    SELECT *
    FROM Enrollment
    WHERE AcademicYear = '2015/16'

My understanding is that MySQL does not support materialized views or filtered indices, so you're not going to get any performance improvements this way. That's probably fine: unless you're managing a worldwide network of universities, your Students table is probably hundreds of thousands of records, and Enrollment is millions. An index on AcademicYear, and maybe a couple covering indices, should provide excellent performance even on inexpensive hardware.

If it does become a problem, then by all means use @eroomydna's suggestion of partitioning.

0

Actually, keeping current and historical data in the same tables saves a lot more than a few where statements can overcome. Keeping data segmented (by year or otherwise) means you have to examine each query and change the target of the query itself. This usually involves dynamic SQL which is (and should be) always the last solution to consider.

I've given some detains of designing versioned and bi-temporal tables and the queries needed to access them, mostly in StackOverflow. There is also a slide presentation available here.

It's a detailed process but it only involves normalization. That is something all db developers and DBMSs know how to work with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.