I am having difficulty designing the database for the following requirement, your suggestions / advice is highly appreciated
There is a Collector Table which illustrates the People who collect the payments for the respective invoices from the Customers.
Problem: There are three ways a debtor invoice is cleared / collected.
- Cash Payments
- Check Payments
Credit Notes
Cash Payments: are pretty straight forward, its a matter of settling an invoice with the cash received
Check payments: if made then the following details of the checks should be recorded.
a) Check 'Post Date'
b) Check Number
c) Check Value
Credit Notes: will be raised against an invoice if they return some item against a specific invoice or if they overpaid a sum beyond an invoice value (trust me people do this)
What i cannot get my head around is how do i normalize a table which can store all three:
should i have one table for all the three types of transactions?
when the customer pays by check how do i go about recording the check attributes (ex: post date, check number, value etc)
Please advice
