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.

  • 2
    Hmm, come on guys, this question is by no means 'too broad'. We may need some clarification about some details but this can easily be answered. So please stop voting to close or at least give a better reason. Commented Aug 1, 2016 at 9:06

2 Answers 2


Lets try to get this info together and try some solution.

To start: Every table has an ID column which can be referenced from other tables. I will assume that the column is named ID and that the referencing columns are tablename_id.

So from what you describe I assume the following tables (and references):


TERMS (academic_year_id)


CLASSES (subject_id, term_id)


ENROLLMENTS (student_id, class_id)

ASSESSMENTS (enrollment_id)

So the enrollments define which classes a student takes. The classes reference the subject and the term. This means the same class must be created newly for each term, so assuming a class is a temporary thing. You could move the term_id from class to enrollment if the classes always are the same though this seems very unlikely.

An assessment relates to an enrollment. So it 'knows' which class (and therefore subject and term) and which student are assessed by the references.

I am not sure from your description if this form of assessment is enough or if there would also be other assessments on higher levels. So please clarify this point.


Anything that happened had a date and time it happened. At the time it happened there were categories that it fell into. An academic year, season, semester, block, whatever.

Since you're doing a relational-database one way to do this is through foreign keys. An academic year could have many assessments pointing to it to show they happened that year. However, that would be silly unless the record for the year had some useful information about that year you're trying to link to. Otherwise it's better to just list the year in the assessment record.

Look up the normal forms. They will teach you how to build these relationships. Be aware that the more complicated the structure is you create the more difficult is is to write queries against it. Also be aware that if you just make one massive table you may as well be writing everything to a text file. The trick is to find the balance in between the exterams.

  • 1
    If this is just about partitioning date ranges, all you need is the date and time it happened. Queries will take care of the rest. If you want to define the date partitions themselves, those belong in a separate table. Commented Jul 31, 2016 at 19:36
  • What i imagined is an Admin dashboard with functionalities like MANAGE SESSION >Create session, MANAGE TERM> Create term. A academic year has 3 terms, each term belongs to a SESSION will contain record of every information that has happened on that term like, students, assessment record, etc.what seem very complex to manage for me is ASSESSMENT, cos i'm planning to maintain not just assessment record of a student, but (detailed) assessment of a STUDENT belonging to a CLASS on a SUBJECT, on a particular TERM of a particular SESSION/ACADEMIC YEAR. Commented Jul 31, 2016 at 19:56
  • I think my question seem hard to understand but i believe that whoever understands it reveal to me a particular secret tip i don't knw that conquers the whole confusion. so far you made sense @CandiedOrange. but the solution i need is missing. Commented Jul 31, 2016 at 19:57
  • One thing to be careful of. A school can start out on the block system and end up on the semester system. Yet students that attended the block system need their records to not change when you change systems. Don't bake any one system into your design. Commented Jul 31, 2016 at 20:44

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.