I have set myself a project of creating a school management system which is to allow for a parent easier access to a 'profile' of their child, showing their progress in school, But i am having a few issues on the design of the database.
Number 1: Grades.
I am not sure how to go about storing each pupil's grades. I was considering having a table which would hold a row for each different subject, and a teacher would insert the grades of only the subjects which the pupil takes, and holding a studentid row which
would connect it to the student. Should i proceed with this idea, or is there a better way to do this ?
In the first 5 years in the school there would be numerous subjects for the students, but for the college years, there would be considerably less.
Number 2: Timetable.
The 'base' timetable would be 5 subjects a day, so i was considering a hard-coded table of mon_1, mon_2, mon_3 and so on, giving me 25 rows in the table for the timetables, and for the college years, putting an option of 'Free' for those periods. Should this
be the approach I take, or again, is there a better option?
That's a fairly basic idea, you could get fancy and combind student and teacher into a people table or something, then have another table for student specific details and teacher specific details - but that's probably a bit much.
So to use this then, you have Classes, that have a ClassSchedule (many records) the Class schedule has the start and end time of the class - so each time the class meets is another record in ClassSchedule that links back to the Class by ClassID. Also, here
you could possibly use ClassID and StartTime as a composite key, instead of having a surrogate CSID - but i'm really not sure of datetime efficiency in keys.
Finally, students are linked to a class and they get one grade, so you store that in their linkage record.
Member
2 Points
10 Posts
Database Logic Help
Sep 29, 2010 09:05 AM|Riddick87|LINK
I have set myself a project of creating a school management system which is to allow for a parent easier access to a 'profile' of their child, showing their progress in school, But i am having a few issues on the design of the database.
Number 1: Grades.
I am not sure how to go about storing each pupil's grades. I was considering having a table which would hold a row for each different subject, and a teacher would insert the grades of only the subjects which the pupil takes, and holding a studentid row which would connect it to the student. Should i proceed with this idea, or is there a better way to do this ?
In the first 5 years in the school there would be numerous subjects for the students, but for the college years, there would be considerably less.
Number 2: Timetable.
The 'base' timetable would be 5 subjects a day, so i was considering a hard-coded table of mon_1, mon_2, mon_3 and so on, giving me 25 rows in the table for the timetables, and for the college years, putting an option of 'Free' for those periods. Should this be the approach I take, or again, is there a better option?
Participant
1785 Points
529 Posts
Re: Database Logic Help
Sep 29, 2010 07:55 PM|PNasser|LINK
Probably want something like
Table: Student { StudentID (PK), FirstName, LastName, etc.. }
Table: Teachers { TeacherID(PK), FirstName, LastName, etc...}
Table: Classes { ClassId (PK), ClassTitle, TeacherID(FK) }
Table: StudentsToClasses { StudentID(PK,FK), ClassID(PK,FK), Grade }
Table: ClassSchedule { CSID (PK), ClassId(PK), StartTime (DateTime), EndTime (DateTime) }
That's a fairly basic idea, you could get fancy and combind student and teacher into a people table or something, then have another table for student specific details and teacher specific details - but that's probably a bit much.
So to use this then, you have Classes, that have a ClassSchedule (many records) the Class schedule has the start and end time of the class - so each time the class meets is another record in ClassSchedule that links back to the Class by ClassID. Also, here you could possibly use ClassID and StartTime as a composite key, instead of having a surrogate CSID - but i'm really not sure of datetime efficiency in keys.
Finally, students are linked to a class and they get one grade, so you store that in their linkage record.
Hope I didn't confuse you too much.
Contributor
3372 Points
1308 Posts
Database Logic Help
Sep 29, 2010 09:43 PM|Lannie|LINK
Or save some development time for this generic process,
and look for MS Access or MSSQLExpress based solutions
that exist for ideas or meeting your needs.
For example:
http://school-maestro-iii.software.informer.com/
There are many others to pick from.