Automatically promote students in a School Management system

[Webville312]

Dear all,

I have this project that I was working on, and I put it on hold.
Now I have revived it (Anyway that is not the issue).
The issue is that I record students' information and record their entry class; However, as time goes on, the students either gets promoted to the next class the following year. Now that is where my challenge is. How do I promote the student without updating their previous records (Given that I need to store their history)?
The one I tried out was updating the students records, and erasing the students' history.

I need some ideas on how I can go about this.

Thanx in advance.

[veedeoo]

I am currently writing a similar applications where Professors and Students are stored in the same table. What separate between them is a column called membership. I defined 3 different membership levels first student, second professor, and third school admininstrator or department head. It also pull out their LinkedIn profile through the API , if given the permission to do so .

The course history, personal background, and professional profile are stored in separate tables. By doing this, I can pull out anything about the members.

To promote the students just update the membership type or code, or you can create some rules that will automatically promote a member to the next level.

[diafol]

I've written quite a few of these in my time too. They can be simple - almost a flat table (not recommended, heh heh), or very involved.

You need to get pretty granular (IMO) with each entity. You will most likely be looking to store specific assessment data (e.g. a certain test) for certain classes of certain teachers in certain courses in certain subjects/faculties/departments.

Historical records should be just that - you can create archive tables if you need to. You don't really need them clogging up your oft-accessed tables if you have millions of them. For example, a student-class table:

stud_id | class_id (compound PK)

Could be emptied at the end of every academic year - the data being appended to prev-student-class (same structure). In our "real" school system is called a "rollover".

Give us an idea of the schema that you have so far and what key info you need to store.

[Webville312]

So, I have three dedicated tables that store students information.
Table one is called student_data; whose definition is as follows;

CREATE TABLE IF NOT EXISTS `students` (
  `Id` int(12) NOT NULL AUTO_INCREMENT,
  `school_id` int(12) NOT NULL,
  `sponsor` varchar(255) NOT NULL,
  `parish` varchar(100) NOT NULL,
  `parish_code` varchar(100) NOT NULL,
  `school` varchar(100) NOT NULL,
  `student_class` varchar(5) NOT NULL,
  `year_of_registration` int(4) NOT NULL,
  `firstName` varchar(30) NOT NULL,
  `lastName` varchar(30) NOT NULL,
  `religion` varchar(100) NOT NULL,
  `regNo` varchar(20) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `date_of_birth` date NOT NULL,
  PRIMARY KEY (`student_id`));

Then I have the student_marks table; whose definition is as follows;

  `marks_id` int(12) NOT NULL AUTO_INCREMENT,
  `school_id` int(12) NOT NULL,
  `regNo` varchar(20) NOT NULL,
  `semester` varchar(10) NOT NULL,
  `subject` varchar(30) NOT NULL,
  `paper` varchar(30) NOT NULL,
  `marks_scored` int(3) NOT NULL,
  `class_id` varchar(4) NOT NULL,
  `year_of_study` int(4) NOT NULL,
  PRIMARY KEY (`marks_id`));

Those are the two main tables that contain a student's information.