title: Database design for school fee processing
body: <p>I need suggestions on a database design.</p>
  
  <p>I am currently working on designing a DBMS for a school. After designing courses and exams tables, now I have come to fees module.</p>
  
  <p>here is what I have done so far.
  I have created 4 tables which are described as below:</p>
  
  <pre><code>fee_type
  -------------
  fee_type_id PRIMARY KEY
  
  fee_type    TYPE OF FEE (MONTHLY, WEEKLY,ANNUAL,ONE TIME)
  
  fees
  -------------
  fees_id PRIMARY KEY
  
  fee_heading  (eg. TUITION FEE,LAB FEE, HOSTEL FEE,SPORTS FEE)
  
  amount       (CURRENT CHARGE OF THE FEE, could change with time)   
  
  class_id     (GRADE ID, GARDE 4, GARDE 5, GRADE 6)
  
  fee_type    TYPE OF FEE (MONTHLY, WEEKLY,ANNUAL,ONE TIME)
  
  archived     (FEE HEADING ARCHIVED FOR USE)
  
  fee_student
  -------------
  fee_id     (RELATED fee_id (FK))
  
  student_id (RELATED student_id(FK))
  
  effective_from (DATE FROM WHEN THE FEE APPLIES TO THE STUDENT)
  
  amount  (CHARGE AT THE TIME OF FEE ASSIGNMENT (applicable to particular student))
  
  discount (DISCOUNT HONORED TO STUDENT IF ANY)
  
  status (ACTIVE OR INACTIVE)
  
  transaction
  ---------------
  id PRIMARY KEY
  
  date (date and time when transaction takes place)
  
  fee_id (PAYMENT FOR)
  
  student_id ({TO BE} PAID BY)
  
  amount ( AMOUNT PAID/APPLIED)
  
  description
  
  cr ( yes or no)
  
  dr (yes or no)
  
  remarks
  </code></pre>
  
  <p>Transaction table will store all the payments by student as well as all the amount charged for that student. </p>
  
  <p>I am thinking of storing amount charged to students in transaction table according to fee_type. that means, if the fee is of type WEEKLY, one record per week will be automatically added to the transaction table and the amount is marked as debit (or credit , whatever) .</p>
  
  <p>Hope it makes sense. </p>
  
  <p>Am I on the right path to design database ?</p>
  
  <p>Your comments and suggestions will be greatly appreciated.</p>
  
  <p>Thank you</p>
  
  <p>Bishnu</p>