List:General Discussion« Previous MessageNext Message »
From:Alvaro Cobo Date:October 25 2005 6:48am
Subject:Microfinance database
View as plain text  
Hi guys. I don't want my job done by anybody else, but I have found no solution for
several days to this problem. 

I am developing a micro-finance system to farmer communities. The problem I have got is in
the database description. 

I have three principal tables: (see SQL at the end). 

1. Loan. # Where I register the loan. 
2. Resources/goods given. # Where I register the goods give in the credit (and I sum all
the resources to know how much money the credit has). 
3. Payments. # Where I register the payments. 

I've got all the formulas to calculate the interest and amortization tables. The problem
is with the penalty/default interest. 

Example: 

Paul has got a loan: he's got a cow and money (total 500). 

He has to pay 80 dollars each month. 75 correspond to capital and 5 to interest. 

If he fails in paying one of the installment, I have to calculate the penalty (which in
this cases is 10% per year) in daily basis. Two days later he has paid part of the
installment (30) so I have to calculate the penalization interest for the two days, then
discount the interest, and the discount the rest from the Principal (capital). Then he
has got 39 as unpaid capital. 

Three days later he has paid 20 dollars. Again, I have to calculate the penalization
interest, and substract it from the capital in debt. And like this until it gets the
payment finished. 

I know all the formulas and stuff, but my problem is in the database definition, and
specifically, where to store the payments, the failed installment and the penalization
interest. 

The questions are: 

1. Should I store the failed installment in a separate table, or in the same payments
table?. 
2. The calculations (interest, failed capital, penalization interest) must be stored in a
table or retrieved using a query?. 
3. The most important question: Does anybody could help me with a link or information
about how might be the structure of a financial database?. 

Thank you very much guys, and again, as you can see I am very confused and I don't my job
done by anybody else. I just need your wise advice. 

Best regards, 

Alvaro. 

TABLES: 

/*Table for the credits. */

CREATE TABLE `tbl04_Credit` (
`PK_Credit` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`FK_ProjectHolderId` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`date_credit` date NOT NULL default '0000-00-00',
`interest` double NOT NULL default '0',
`tiempo_credito_days` double NOT NULL default '0',
`periodicity_credit` int(11) NOT NULL default '0',
`nom_responsable` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`comment_credit` varchar(255) collate latin1_spanish_ci default NULL,
`last_change` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`PK_Credit`),
KEY `FK_ProjectHolderId` (`FK_ProjectHolderId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

INSERT INTO `tbl04_Credit` VALUES ('1', 'Community One', '2005-10-25', 12, 360, 30, 'Paul
Simon', 'No comments', '2005-10-25 01:35:24');
INSERT INTO `tbl04_Credit` VALUES ('2', 'Community Two', '2005-10-27', 12, 360, 30, 'Art
Garfunkel', 'No comments', '2005-10-25 01:35:05');

/*  Resource table   */

CREATE TABLE `tbl04_CreditResource` (
`PK_Cred_Recurso` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`FK_Credito` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`resource` varchar(60) collate latin1_spanish_ci default NULL,
`UnitValue` float NOT NULL default '0',
`Quantity` float default NULL,
`penalty_interest` float NOT NULL default '0',
`credit_time` int(11) NOT NULL default '0',
PRIMARY KEY (`PK_Cred_Recurso`),
KEY `FK_Credito` (`FK_Credito`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci ;

INSERT INTO `tbl04_CreditResource` VALUES ('Resource1', '1', 'Duck', 20, 8, 12, 360);
INSERT INTO `tbl04_CreditResource` VALUES ('Resource2', '1', 'Chicken', 5, 8, 12, 360);
INSERT INTO `tbl04_CreditResource` VALUES ('Resource3', '2', 'Cow', 250, 1, 12, 360);
INSERT INTO `tbl04_CreditResource` VALUES ('Resource4', '2', 'Chicken', 5, 10, 12, 360);


/*Payments*/

CREATE TABLE `tbl05_Payment` (
`PK_Pago` int(11) NOT NULL auto_increment,
`FK_Cred_Recurso` varchar(255) collate latin1_spanish_ci default NULL,
`installment_number` int(11) NOT NULL default '0',
`end_installment_date` date NOT NULL default '0000-00-00',
`payment_date` date NOT NULL default '0000-00-00',
`payment_capital` float default '0',
`payment_interest` float default '0',
`unpaid_capital` float default '0',
`interest_unpaid_capital` float default NULL,
`comentario_pago` varchar(255) collate latin1_spanish_ci default NULL,
`ultimo_cambio` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`Estado` enum('1','0') collate latin1_spanish_ci NOT NULL default '0',
PRIMARY KEY (`PK_Pago`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;




-------------------------------------
"If you have an apple and I have an apple and we exchange these apples then you and I will
still each have one apple. But if you have an idea and I have an idea and we exchange
these ideas, then each of us will have two ideas"
--George Bernard Shaw--

Thread
Microfinance databaseAlvaro Cobo25 Oct