A payment payment looks like a simple topic, but it can expand after you begin to consider the details.
The process of paying bills at its simplest includes two data objects: an account (the so-called client, client, etc.) and a register. The statement writes down credits and debits to the client's account. Debits are invoices and any document that increases the customer's balance. Loans are payments and any document that reduces a client’s balance. There are exceptions to this, but let's be simple.
The amount of loans and debits is the balance in the client’s account.
One part of your system is to generate subscription fees, product purchases, etc.
The following is an example table structure:
Customer Table Id Name Balance 1 Smith 1000.00 2 Jones 0.00 Ledger Table Id CustomerId Type Description Amount 1 1 D Services for June '09 1000.00 2 2 D Subscription for Jul '09 50.00 3 2 C Payment MC ****2908 -50.00
Of course, this is about as simple as you might want.
Here are some other things to consider.
Triggers on tables can be used to synchronize customer balance. If you need to know which payments apply to which accounts, you need an additional table.
Most large and not very large accounting systems support two parts for this process: documents and registers.
Documents consist of objects such as invoices, credit memos, sales receipts, refunds, and payments. There are others.
Depending on the document, different methods affect the book.
Invoices: to charge a customer, but not to collect money, it is because of you, therefore there is only one entry in the book - debit for the invoice amount
Credit memos: lend to the customer, but do not return the money, you must return it, so there is only one entry in the book - credit for the amount of the credit memo
Proceeds from sales: charge a client and collect money, the net effect should have a zero balance due to this action, therefore there are two entries in the register: debit for the sale amount and credit for the netting payment amount to zero.
Payments: register the collection of money from the client, so only one entry in the book is a loan for the amount of payment.
Hope this touches on your question. If you don’t tell me how I wrote about a dozen user accounting systems over the years.