Database design for school management to keep track of current and previous school years

Question

What's the best way to design a students registration records and keep tracks of previous academic years? I have a table to enroll students, something like this:

CREATE TABLE [dbo].[Students] (
    [StudentID]          INT             IDENTITY (1, 1) NOT NULL,
    [StudentNumber]      NVARCHAR (50)   NOT NULL,    
    [EnrolmentDate]      DATE            NULL,
    [Class]              NVARCHAR (50)   NOT NULL,
    [StudentSurname]     NVARCHAR (60)   NOT NULL,
    [StudentFirstNames]  NVARCHAR (60)   NOT NULL, 
    [FatherSurname]      NVARCHAR (100)  NULL,
    [FatherNames]        NVARCHAR (100)  NULL,    
    [MotherSurname]      NVARCHAR (100)  NULL,
    [MotherNames]        NVARCHAR (100)  NULL,
    [SchoolYear]         NVARCHAR (50)   NOT NULL, 
    [StudentPicture]     VARBINARY (MAX) NULL,
    CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ([StudentID] ASC),
    CONSTRAINT [IX_Students] UNIQUE NONCLUSTERED ([StudentNumber] ASC),
    CONSTRAINT [FK_Students_AcademicYear] FOREIGN KEY ([SchoolYear]) REFERENCES [dbo].[AcademicYear] ([SchoolYear]) ON UPDATE CASCADE
);

I also have other tables for SchoolFees, MarksEntry, SchoolYear, etc. So far everything works well for current year, I an just select where SchoolYear = current year, whether it's list of students, ,marks or fees paid/unpaid.

The challenge is to access data from previous years, I haven't figured out how to design the database or add a column to keep track of previous year. When a student passes the exam, he/she promoted to a different class, so what I update in the Students table is: Class and SchoolYear, the rest of data remain the same like student number and other details. So if I have a student in class 1B in school year 2020-2021 and promote to class 2B in year 2021-2022, the records of this student in class 1B in 2020-2021 are still in database but no longer accessible because that student no longer exist, their records have been modified. What's the best and easy solution not to duplicate a student records but somewhat keep track of previous classes and school years when promoting a student to a different class? How to promote the student to a different class?

Thanks

Answer 1

The Student table is a master table; generally speaking, information about the student is static and would not change. I would recommend adding children tables to track the student activities/matriculation in or out of classes. All this detail data would be inserted or updated as time advances.

From the hip, I'd start with something like this:

/* Student demographic data */
CREATE TABLE Student
(
    StudentID  INT IDENTITY(1, 1) PRIMARY KEY
    ,FirstName VARCHAR(100)
    ,LastName  VARCHAR(100)
    ,DOB       DATE
    ,Address   VARCHAR(150)
    ,/* etc */
);

/* Information on classes */
CREATE TABLE Class
(
    ClassID       INT IDENTITY(1, 1) PRIMARY KEY
    ,AcademicYear VARCHAR(25)
    ,StartDate    DATE
    ,EndDate      DATE
    ,CourseNumber VARCHAR(50)
    ,/* etc */
);

/* What action was taken with the class */
CREATE TABLE ClassActivity
(
    ClassActivityID INT IDENTITY(1, 1) PRIMARY KEY
    ,Activity       VARCHAR(50)  /*Enroll, Drop, etc*/
    ,/* etc */
);
   
/* Classes students interacted with (took action in) */
CREATE TABLE StudentClass
(
    StudentClassID   INT IDENTITY(1, 1) PRIMARY KEY
    ,StudentID       INT
    ,ClassID         INT
    ,ClassActivityID INT
    ,EffectiveDate   DATE
    ,/* etc */
);

/* How students performed in class */
CREATE TABLE StudentClassGrade
(
    StudetnClassGradeID INT IDENTITY(1, 1) PRIMARY KEY
    ,StudentClassID     INT
    ,Grade              VARCHAR(10)
    ,EffectiveDate      DATE
    ,/* etc */
);

It would also be useful to create views to display the student's current/past enrollment as separate queries instead of trying to handle these questions in the data tables. Such as:

dbo.v_StudentCurrentEnrollment
dbo.v_StudentHistoricalEnrollment
dbo.v_StudentCurrentClassLevel

The logic for which class the student is in would be contained in the view, not in the data tables. Structure your tables with solid relationships, then you can ask well formed questions to answer myriad questions.