I am building a kind of School Management System.I want to be managing the database records of things like assessment, term/season, academic years. every info must belong to a particular academic year so at anytime the admin can query for info pertaining to a particular year. Assessment too can belong not only to an academic year, but term, class, subject, student too.I have been figuring out on how to develop a scalable database schema for this. I thought of having separate databases for each year, or combine them each on a very different table but how to properly reference each assessment down to a particular student, subject, class, term and year is still my confusion over the whole matter.
How can I maintain a database record of an entity that belongs to several categories of another entity, per year, per season, etc
php;web development;database design;relational database;scalability