Click here to Skip to main content
15,039,244 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
What's the best way to design a students registration records and keep tracks of previous school years?

What I have tried:

I have a table 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 [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?
Updated 24-May-21 21:49pm
Gerry Schmitz 24-May-21 13:38pm
You got your "student table" ... you need a date-driven student-class / attendance / performance record for the "history". If a student "no longer exists", then you have data retention / design issues.
katela 24-May-21 16:36pm
date-driven records, I don't get it. any hint?

If I understand your question correctly, you have a many-to-many relation at hand. One student can participate in several years and on the other hand one year has several students.

In such case, instead of trying to introduce new columns, you need to define a junction table to hold proper references.

Have a look at
- Many-to-many (data model) - Wikipedia[^]
- Associative entity - Wikipedia[^]
katela 24-May-21 16:19pm
Thanks for suggestions. I understand this part with a junction table. How will it work when I update an existing student to a new class and new school year? Any hint?
Wendelius 24-May-21 22:45pm
In terms of SQL you wouldn't update the record. Instead when the student moves to then next year, you add a new record. This way you have both current situation and the history in the junction table.

So for an individual student who is studying the third year the junction table could contain

studentid yearid
--------- ------
57 2019
57 2020
57 2021
Maciej Los 25-May-21 3:14am
katela 25-May-21 5:57am
Thanks a lot. Very clear.
Wendelius 25-May-21 22:20pm
You're welcome :)
Wendelius 25-May-21 22:20pm
Thank you
Mika Wenedlius already mentioned about relational database. So, in addition to His answer:

CREATE TABLE [dbo].[Students] (
    [StudentID]          INT             IDENTITY (1, 1) NOT NULL,
    [StudentNumber]      NVARCHAR (50)   NOT NULL,    
    [EnrolmentDate]      DATE            NULL,
    [Class] -- ?!? 

Well... Class is not a part of student (attribute). Class is an object which describes a collection of students (but knows nothing about students!):

YearID -- refers to year's table  (^__b style="color:orange">YearID - PK, ^__b style="color:orange">StartY [start of school year], EndY [end of school year])
... and so on..

Finally, you have to create a third table, which should store all students in a class - Class_Student table:
 -- refers to class's table
^__b style="color:blue">StudentID -- refers to student's table

This is how many-to-many relationship should be defined in your case. As a picture is worth thousands of words....

-----------       -----------------       ---------
| Student | 1---∞ | Student_Class | ∞---1 | Class |
-----------       -----------------       ---------
katela 25-May-21 6:00am
Thanks a lot Maciej. Now it's very clear.
Maciej Los 25-May-21 7:16am
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900