AbdulBazith's avatar

Problem in fetching records from db. need suggestion for columns in table

Guys iam working with a project school management system.

i have completed 2 modules (student registration and sms panel)

Now started my 3rd module fee payment.

the fee payment takes two steps,

1)fee structure and 
2 )fee payment

In fee structure

the school posses Main category and sub category of fee, like below structure

Term1     payment duration (1-08-2019 to 15-08-2019)
    school Fee =  6000
    Bus Fee   =  3000
    Tution Fee= 2500

Term 2  payment duration (1-12-2019 to 15-12-2019)
    school Fee = 6000
    Lab Fee   =   3000

Term3   payment duration (1-03-2020 to 15-08-2020)
    School Fee= 6000
    extra fee = 2000

like this it goes on,

this structure may varies class wise (1 std 2nd std and so on...)

So for this i planned two tables to add the main category and the sub category.

here are the images

main category table: https://imgur.com/XO1B2le

sub category table: https://imgur.com/fNd9idT

so the fee structure is ready. every thing ok i think so.

Next lets move to Fee payment process

in fee payment form i have class(dropdown), section(dropdown), student name(dropdown).

when class is selected, its relevant section is selected and when section is selected the students of that section are listed. so when that specific student is selected, what i expect is the term1 and term2 that is from the fee main category, its name and its sub categories must be listed with amount to pay the fee for that student.

i have a form like this: https://imgur.com/mRqNVE6

when serach button is clicked this below portion in the image will be fetched from table. but my doubt is from which table the fee main caegory and sub category are fetched??? some student will keep the first term fee pending (few amt) and they will pay in second term. some will pay the whole fee in single slot itself.

When the year, class, section name are selected, the below portion must be rose with the tem1 and term2 info below.

my doubt is from which table it must be fetched. some students may pay the fee in a single slot for whole year, some student may pay the fee for that term. some student may pay the fee in installment. that is if termfee is 10000 means, 8000 they will pay, 2000 they will keep the balance, and they will pay in next term.

My doubt is from which table it must fetch the record of fees. in which table the fee details must be saved. what columns i should have in feepayment table?? please,

Kindly some one help please

0 likes
4 replies
AbdulBazith's avatar

These are the columns to be saved after fee payment

acc_year_id (academic year)

class_id 

section_id

student_id

bill_no

payment_date

any_notes  (any notes can be written)

cash_type (fee paid by cash or cheque just the type can be mentioned)

discount_amt  (if any discount amount is given to that student)

fine_amt (if any fine )

balance_amt (some times parents may pay only 3/4th of the amt like if 10000 is there they will pay 8000 and keep the balance 2000 to pay in next term that is term2. like installment payment.)

fee_main_cat_id (the main category like term1 or term2)

fee_sub_cat_id (sub categories for the main category term1 bus fee, school fee, extra fee etc..)

tota_amt_of_fee_main_cat (the actual amt for the main category say like 10000 rupees)

payment_status (paid /or /not)

all the above columns i must save. my doubt is do i need all the above columns?? else shall i can remove few?? how i can make this in two tables or in a single table.

and i expect the output as like below. if the parent need the receipt as printed format the printed format should be like below

Term1
    School Fee      5000
    Bus Fee         2000
    Lab Fee         2000
    Extra fee       1000

        Total       10000


Term2 ...  (if the parent pay the term 2 fee also this will be filled same as term1)

// if the parent kept any balance means it should be given like below

Term1
    School Fee      5000
    Bus Fee         2000
    Lab Fee         2000
    Extra fee       1000

        Total       10000
        Paid          8000

        Balance       2000

and this balance is carried to next term fee that is for term2 fee. thats why i given a balance textbox in the fee payment form.

what i planned is dividing it in two tables

Table 1: Student_fee_transaction_master with columns

acc_year_id 

class_id 

section_id

student_id

bill_no

payment_date

any_notes 

cash_type 

Fees_original_amt 

discount_amt 

fine_amt 

balance_amt 

fee_main_cat_id

Table 2: Student_fee_transaction_details with columns

id
bill_no_id (from master table)
fee_main_cat_id
fee_sub_cat_detail 

// this table fully says abt the fee sub category details

is all this right??

kindly some one hlep please??

AbdulBazith's avatar

@snapey

Sorry for tagging you, can you suggest any idea.

i thought all the possible ways and mentioned in this thread. getting some what confused.

AbdulBazith's avatar

@snapey just give your suggestion that's enough

I have finalized the table and given in the below image

Refer: https://imgur.com/E12vRGr

is this right?? do i need another table? if the parent kept a balance of 1000 rupees in the term1 means, it must be fetched in the term2. thats why i kept a text box old balance in the form.

is this right??

michapietsch's avatar

@abdulbazith I understand your main concern is to carry forward the balance from previous fee invoices and payments. I would suggest to strictly separate invoices and payments. So you could always sum up the fees total and compare it to a payments total. You would then show a calculated balance.

Then the user would make another payment or the school would pay him back. (You then have to decide if you handle refunds in an additional table or as negative payment values.)

You only run into problems if you want to match payments with invoices. Consider someone accidentally filling in the wrong fee/invoice number. It can become a mess. You can take the burden from the user if you can only provide them a list of fees and payments and ask them to make a balancing payment, and the school would register the incoming payment for the student name.

Please or to participate in this conversation.