Pfthb

I have four table as below:

FEES

fees_id, interest, amount, total_amount, status_id, is_recurring recurring_status from_date to_date payment_id
 F001 1.50 1000 1015 1 N NULL 2018-11-01 2018-11-01 1
 F002 2.00 2000 1020 1 Y COMPLETE 2018-11-01 2018-11-20 2

PAYMENT

id, amount, payment_date, txn_id, bnk_name, txn_status, pay_mode, dd_no, dd_date, chk_no, chk_date
1 1015 2018-11-11 TXN0001 SBI 1 1 NULL NULL NULL NULL
2 1020 2018-11-20 NULL NULL NULL 3 DNO001 2018-11-19 NULL NULL

PAY_MODES

id name display
------------------
1 ONLINE Y
2 CASH Y
3 DRAFT Y
4 CHECK Y

PAYMENT_STATUS

id status display
-------------------
1 PAID Y
2 UNPAID Y

A fee can be paid by any of the four pay modes.

I have few questions:

• Is it ok (in this scenario) to have one payment table for all pay modes OR is there any better option?


• A fee can be recurring (paid every month until its recurring status is completed). How can I handle these payments?


• Do I need to store each payment response (in case of online payment) whether success, fail or whatever. If yes, should I use a separate table or store in a file?

Que: Is it ok (in this scenario) to have one payment table for all pay modes OR is there any better option?

Yes, all the payment should be in one Payment Table. This helps to
maintain the transactional integrity constraint. Also, it will helpful
for you in future while generating various reports

Que: A fee can be recurring (paid every month until its recurring status is completed). How can I handle these payments?

You should have another table for maintaining the recurring payments
record as Subscription table and at every recurring payment event of a
record insert new entry into payment table. So, One subscription
record will be related to multiple payment entries.

Que: Do I need to store each payment response (in case of online payment) whether success, fail or whatever. If yes, should I use a separate table or store in a file?

Yes, you should store the payment response in the same payment table
record. This will help you out in the failover detection as well as
major help in Audit Log.