title: database design for a payment system
body: <p>I have four table as below:</p>
  
  <p><strong>FEES</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>PAYMENT</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>PAY_MODES</strong></p>
  
  <pre><code>id  name   display
  ------------------
  1   ONLINE Y
  2   CASH   Y
  3   DRAFT  Y
  4   CHECK  Y
  </code></pre>
  
  <p><strong>PAYMENT_STATUS</strong></p>
  
  <pre><code>id  status  display
  -------------------
  1   PAID    Y
  2   UNPAID  Y
  </code></pre>
  
  <p>A fee can be paid by any of the four pay modes.</p>
  
  <p>I have few questions:</p>
  
  <ul>
  <li>Is it ok (in this scenario) to have one payment table for all pay modes OR is there any better option?</li>
  <li>A fee can be recurring (paid every month until its recurring status is completed). How can I handle these payments?</li>
  <li>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?</li>
  </ul>