Based on this logic I came up with this...there are a lot of tables but most of the task is handled from front end program. Like programs for an acyear could be copied to any acyear automatically etc.
1. There is an academic year (.e.g. 2011) which can have any number of terms based on the colleges requirement. And each term can have any number of semesters.
2. New student registration is allowed at Term level only. Once they register, they can signup/register for semesters.
3. The college determines certain fees on term and semester level. Also, students can study at evening and day shifts. The number of courses he can take is based on his shift of study when registering as new student first.
4. Student can exempt a course if the counselor believes his previous educational background is enough for the specific subject.
5. Student can select courses he wants to study at any specific semester.
6. The college determines which courses are avaliable in which term and for which program of study (.e.g. IT - Diploma, Business Law - Certificate) and a student can select courses only avalible to his department.
Now, the college have fee requirement and I have handled it in a rather nasty way. I am trying to fix it so much. A student can pay full tutition fee for his choice of program of study. He can chose to pay semester by semester. By semeter, it will be for the number of courses he chose to study in that semester. In my trial, the payment is handled in a table recits which stores record of students who paid part of a tuition fee.
Also, how would I go handle students who finisehd their studies? Should I just add a field in tblStudents a field or move them to another table with some reference to tblStudents.
I am quite stuck at those two main points mainly but look at my design, which is working fine so far but may be you guys can tweak it for better.
If there is anything unclear, please let me know. I will try to elaborate.