Review of database for microloans
I am writing this in English, because I have a 'partner in crime' who prefers this language.I am as volunteer helping an organisation with the purpose to organise 'microloans' to entrepeneurs that cannot get commercial loans, because they cannot provide any collateral. (I do not seek comments about this purpose - I know it is controversial.)
The loans will initially be provioded in Uganda. The organisation has a bank balance there. When a loangiver in Dennmark transfers an amount to the organisation this amount will remain in Denmark, and the organisation authorises by email the contact person in Uganda to make a withdrawal and pay it out to the loantaker. The installments the loantaker pays are deposited in the bank, and the contact person reports the installments by email to the organisation in Denmark who then reimburses the loangiver. International transfers are thereby avoided to reduce costs.
The platform is Joomla using mysql for database. The organisation has chosen paypal for money transfers. I envisage using the following tables. Here is a picture of the tables and their relationships: http://christianjorgensen.be/Billeder/tables.jpg?_cache=1329153452
A. loancase (lånesag) where loancases are established with id, title, goal (mål), start- and end date. The loancase will have an B.installmentplan (afdragsplan), for example a loan of 1200kr started 1 February expects installments of 430kr at the end of February, March, and April (the excess of 3x30kr goes to cover expenses and expected exchange rate losses.) A loancase includes more data that falls outside the scope of this question.
C. lender (långiver) where individuals are established as lenders first time they provide a loan.
D. bid, an amount one lender provides for one loancase. A loancase for 1200kr can be funded, for example, by three bids, 200kr, 600kr, and 400kr from three different lenders. A lender can provide bids for several loancases. Bids are established when lenders from their paypal accounts transfer amounts to the paypal account of the organisation. Paypal identifies payers and payees with email accounts, and because one lender can use different paypal accounts with different email addresses for the bids, the bid record must include the email address used.
So far so good. The loantakers hopefully pay the installments, and when they do, the organisation receives reports from the Ugandian contact person. The organisation shall then, manually in the beginning, create an entry in the table E.installment (afdrag). Each installment relates to one loancase. (Queries on the tables installmentplan and installment can give reports on which loancases have installments overdue.)
There are no direct relationship between installments and bids. A loan of 1200 kr can for example have been funded by three bids of 200, 600, and 400 kr and have three installments of 400 each. The organisation will manually (for a start) monitor the flow of installments, and when enough installments have been received for a loancase to reimburse a bid the organisation will create an entry in the table F. return (retur). Normally there will be one return for each bid, but it must be possible to have one bid covered by the sum of several returns. The loangiver will by email be informed and may request to be reimbursed or may donate the installment to the organisation. The return therefore go through varies status' such as created, lender informed, lender reimbursed, donated. If the lender asks for reimbursement the amount will be transferred from the organisation's paypal account to the same paypal account from where it came.
If you are still with me, here is what I ask: A review of and comments to the proposed table and data structure. One possible change could be, noting that the tables B. installmentplan (afdragsplan) and E. (afdrag) have the same structure, to drop one table and add a column 'type' to indicate whether an entry is plan or actual. You may think of other changes. I shall later add additional tables to include the financial transactions, so that it is becomes possible, among others, to verify if the balance on the paypal account agrees with the sum of bids and returns that have passed through it.