支付系统的数据库设计 [英] database design for a payment system

问题描述

我有四个表，如下所示:

I have four table as below:

费用

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

付款

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.

我有几个问题:

• (在这种情况下)可以为所有付款方式使用一张付款表吗?还是有更好的选择?
• 可以重复收费(每月支付一次，直到其重复状态完成为止).我该如何处理这些付款?
• 我是否需要存储每个付款响应(如果是在线付款)，无论是成功，失败还是其他.如果是，我应该使用单独的表还是将其存储在文件中?

推荐答案

问题:(在这种情况下)可以为所有支付模式使用一张支付表吗?还是有更好的选择?

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:可以重复收费(每月支付一次，直到其重复状态完成为止).我该如何处理这些付款?

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:我是否需要存储每个付款响应(如果是在线付款)，无论成功，失败还是其他.如果是，我应该使用单独的表还是将其存储在文件中?

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.

这篇关于支付系统的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！