All we need is an easy explanation of the problem, so here it is.
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂