Friday, February 3, 2012

MySQL question on stackoverflow



i am creating a student management system, i have 3 tables, student, fees, student_fee, the fee table contains the amount of fees and the student_fee contains reference to student.studentid and fee.feeid, so that whenever a student paid their fees, the studentid, feeid and paid_date will be insert into the student_fee table. Fees can increase the next year, how can i still keep the old records of fees payment without losing and causing any problem to the account

I am creating a student management system. The database has 3 tables: student, fees, student_fee. The fee table contains the amount of fees. The student_fee table contains reference to student.studentId and fee.feeid. Whenever a student pays their fees, we insert the studentid, feeid and paid_date will be insert into the student_fee table. Fees can increase the next year but are only effective from the date onwards. I want new entries to show the updated fee but I don't want any changes happening in the old entries. How can I still keep the old records of fees payment without losing and causing any problem to the account. 

For example: Let us consider I have just one student with student id 1 (His name is Jeff Attwood but everyone calls him user one). Let us also consider the fee for 2011 was a dollar (in my dreams). So the student table has one entry -- 1. The fee table also has one entry -- 1. The student_fee table has one entry of when user one paid his fee for 2011. Now, 2012 rolls around and the fees have increased to (drum roll) two dollars. How do I configure my database so that it does not update last year's record (because he paid a dollar last year) but will show the updated fee for this year when I create a new entry? Note that this is an oversimplification and the case will be more complicated in real life. 

No comments:

Post a Comment

Please be kind.