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--
