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
Best Answer
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:
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:
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.