Your comments on this school database system please

[crazycat503]

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.

[jzwp11]

Since there are many fees per term or semester that describes a one-to-many relationship. You currently have the fees as fields in the term/semester table. The fees should be records in a table. You can have a field in the fee table that distinguishes the type (term or semester) fee

tblFees
-pkFeeID primary key, autonumber
-txtFeeName
-fkFeeTypeID

tblFeeTypes (term, semester etc.)
-pkFeeTypeID primary key, autonumber
-txtFeeType

Now, can the number of fees vary from one term or semester to the next?

You will at some point have to assess the fees to the student (a student will have many fees to pays so a one-to-many relationship) and tie that to your receipts table.

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 would opt for keeping the students in the student table even if they have finished their studies because they might come back for additional courses or for another/advanced degree. I would go with a field to identify whether a student is finished or not; perhaps a date field (current students would not have a date (null), so you could use that as a filter).

[crazycat503]

thanks for your reply. The main thing is that the college changes its fee on term and semester levels. For e..g ID Card and application fee (for first timer) is defined in term, while fees like couse per credit (the same for each course), course drop fee are defined each semester. their values may stay the same thru out the year but you never know if they decide to change them next semester. it is why I decided to have the fee for each term and semester there.

And I forgot...the program seems to be working fine for a student who chose a department. But say the student chose ICT-Diploma but have to start with freshman program. Do you think the current design is ok for such? I tried some queries and it worked but I am concerned about its speed and database size when real data gets entered.

[jzwp11]

You would still need to relate the fee to the semester/term

tblSemesterFees
-pkSemesterFeeID primary key autonumber
-fkSemesterID foreign key to your semester table
-fkFeeID foreign key to tblFees
-FeeAmt

and a similar table for term

And I forgot...the program seems to be working fine for a student who chose a department. But say the student chose ICT-Diploma but have to start with freshman program.

Not knowing much about your application, why can't you just consider ICT-Diploma as a department or associate if with a department of some type? (I'm just not sure how to answer that since it is very specific to your application and I don't know how it differs from everything else.)