Loan Management System


FACTS for the Data Model for a Loan Management System

Back to the Conceptual Data Model.
Original Requirements from the User :-

A database for a Loan Management system ...
The database collects informartion about Customers who borrow money from a company.
Every month the customer must pay at least the capital or the interest rate.

Customers :-
customer information includes contact details and balance fields,
which will tell you how much the customer owes.

collector :-
collector is the person who gives the money to the customers and
collects it later point of time.

loangiven :-
loan given is actually the loan given by the company to the customers.

payment :-
payment received by the company.
The database will keep track of payments made by customers.
Payments can be either the interest or/and capital.

Customer (TABLE)
Customer_No (primary)
Customer_Name
Address
Balance

Collector (TABLE)
CollectorID (Primary)
CollectorName

LoanGiven (TABLE)
TransactionID (AutoIncrement id & primary)
Customer_No
DateLoanGiven
AmountGiven
InterestRate
CollectorID (foreign key - from collector table)

Payment (TABLE)
ReturnID (AutoIncrement id & primary)
Customer_No
PaymentDate
PAymentMonth
CapitalPaid
InterestPaid
Remarks

-----------------------------------

Report Requirements :-
Produce a list of those who never pay for a duration.
The criteria is balance should be greater than zero.

now, i have a sql statement to find those who didn't pay. but, it actually working when the customer paid at least paid once.
Suppose, the customer didn't pay at all, that particular record is NOT coming.

Also a list of those who never pay a single payment.
Currently, I am using Data Report and Data Environment to do all the reports from Visual Basic.

So, the output of the report should be single record for each customer as follows :
customer_no
Customer_Name
Address
Balance
CollectorID
CollectorName
PaymentDate (the last payment record - if any)
PAymentMonth (the last payment record - if any)
CapitalPaid (the last payment record - if any)
InterestPaid (the last payment record - if any)

Things of Interest in the Scope of the Data Mode :-

Collectors
Customers
Loans
Payments

What are the relationships between these Things of Interest :-

A CUSTOMER receives a LOAN - Customer agrees to a 'LoanContract' for a Loan with a Collector.
This Contract then generates the need for a series of 'LoanPayments'.
A variable number of Payments with different amounts would be shown as a series of 'PaymentsDue' record in a new table.
To get the total of Capital Paid and Interest Paid, these fields would be added to the LoanContract table.

What are the characteristics of these Things of Interest :-

A LOAN has an Amount.

Here's some notes from www.dbforums.com ...

I have a long loan application that I am modeling.
The form has a collateral section that I know I should break out into a seperate table.
Each record will have Property Description, Model Number, and value for example.
But what about Phone numbers.
Is it OK to have homephone, workphone, cellphone or should you have another table. PhoneNumber, PhoneLocation?
Of course then you probably should add an AllowedPhoneLocation table.
What about MothersName, MothersPhone, FathersName, FathersPhone. Or should I have a Relatives Table with Name, Relationship.
Does either of these solutions break any of the 5 laws?
If not how do you choose which way to go?
On a related topic.
Should Pick tables be created for even simple values or coded into the combo box select option?
For example: Sex: Male, Female.
Relationship: Mother, Father, Friend, Other.
Or Marital Status: Married, Single, Divorced.

What are the advantages or disadvantages of each of these options? On what criteria do you base your choice.

Barry Williams
Principal Consultant
Database Answers