Loan Management System with SMS Database Design

Loan Management System with SMS Database Design

Introduction

The manual technique of loan management has a number of flaws that need to be addressed. A couple of drawbacks include the fact that it is extremely time-consuming and that it is susceptible to human mistake. Keeping track of all of the loans that are currently outstanding, as well as determining the most efficient approach to allocate available funds among the numerous loans, can also be challenging to manage. For the final point, scaling up or down the manual technique of loan management can be problematic, depending on the demands of the institution in question.

Loan Management System is an online platform designed and developed in PHP, MySQL and Bootstrap, the said project aimed to convert the manual process of loan management into an automated systems that stores and organizes the records in a database driven application. Core functions of the system are to provide a fast, accurate and secure transaction between the customers and the management. Specifically, the system can manage the payments and accounting, contract management, report generation, and notification through SMS or text. The study was conducted in a cooperative with 10 employees and more than 100 members that serve as the respondents of the study.

This software program, known as the Loan Management System with SMS Database, is used by a lending organization to keep track of all of its loans and its borrowers. Information about loans is stored in the system, including loan amounts, loan dates (including interest rates), and repayment schedules (containing length of time required to repay the loan). As well as borrowers’ names, addresses, and social security numbers, the system also keeps information about the borrowers themselves. System access is possible through a computer or through a mobile device.

Database Tables

This article will provide you with an idea about the travel agency and booking system database design.

tbl_member – this table will store the information of the members in the system.

• member_id -primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• first_name – the member’s first name
• middle_name – the middle name of the member
• last_name- the member’s last name
• complete_address – the complete address of the member
• contact_number – the contact number of the member preferably mobile or telephone number
• email_address – the email address of the member
• gender – the member’s gender
• civil_status – the member’s civil status
• birthdate – the date of birth of the members
• age – the member’s age
• profile_picture – this will hold the profile picture of the member
• username – the preferred username of member used t login in the system
• password – the preferred password of the member combined with the username to login to the system.
• account_status – (0)active,(1)inactive

Create SQL Statement – the statement below is used to create the tbl_member, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_member` (
`member_id` int(11) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`complete_address` varchar(200) NOT NULL,
`contact_number` varchar(15) NOT NULL,
`email_address` varchar(50) NOT NULL,
`gender` int(1) NOT NULL,
`civil_status` int(1) NOT NULL,
`birthdate` date NOT NULL,
`age` int(3) NOT NULL,
`profile_picture` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_loan_type – this table will store the information of the  loan types in the system.

• type_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• loan_name – the name of the specific loan
• description – description or additional information about the loan type.

Create SQL Statement – the statement below is used to create the tbl_loan_type, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_loan_type` (
`type_id` int(11) NOT NULL,
`loan_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_loan_application – the loan application details will held in this table.

• application_id primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• control_number – unique control number for the loan application
• member_id – this is a foreign key that points out to the member
• loan_type_id – this is a foreign key that points out to the loan type
• mode_of_payment – (0)over the counter, (1)bank transfer, (2)salary deduction
• loan_amount – the total amount of the loan
• loan_duration – the period or duration for the loan to be paid
• purpose – the purpose of the loan
• loan_status – (0)approved, (1)disapproved
• remarks – additional information about the loan application
• processed_by(user_id) – this is a foreign key that points out to the user that processed the application.

Create SQL Statement – the statement below is used to create the tbl_loan_application, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_loan_application` (
`application_id` int(11) NOT NULL,
`control_number` varchar(30) NOT NULL,
`member_id` int(11) NOT NULL,
`loan_type_id` int(11) NOT NULL,
`mode_of_payment` int(1) NOT NULL,
`loan_amount` float NOT NULL,
`loan_duration` int(3) NOT NULL,
`purpose` varchar(50) NOT NULL,
`loan_status` int(1) NOT NULL,
`remarks` varchar(50) NOT NULL,
`processed_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_loan_amortization – this table will store the loan amortization details in the system.

• amortization_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• control_number – a unique control number for the loan amortization
• date – the date when the loan amortization was processed
• payment_amount – the amount paid for the loan amortization
• interest_paid – the interest paid for the loan amortization
• principal_paid – the principal amount to be paid
• remaining_balance – remaining balance for the loan amortization

Create SQL Statement – the statement below is used to create the tbl_loan_amortization, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_loan_amortization` (
`amortization_id` int(11) NOT NULL,
`control_number` varchar(30) NOT NULL,
`date` date NOT NULL,
`payment_amount` float NOT NULL,
`interest_paid` float NOT NULL,
`principal_paid` float NOT NULL,
`remaining_balance` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_loan_payment – this table will store information of the loan payments in the system.

• payment_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• payment_reference_number – this is a unique reference number for the loan payment
• member_id – this is a foreign key that points out to the member
• date – date for the loan payment
• payment_amount – the amount paid for the loan
• proof_of_payment – upload documents for the proof of payment
• payment_status – (0)pending, (1)accepted, (2)rejected
• remarks – additional information about the loan payment
• review_by(user_id) – this is a foreign key that points out to the user who review the loan payment

Create SQL Statement – the statement below is used to create the tbl_loan_payment, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_loan_payment` (
`payment_id` int(11) NOT NULL,
`payment_reference_number` varchar(30) NOT NULL,
`member_id` int(11) NOT NULL,
`date` date NOT NULL,
`payment_amount` float NOT NULL,
`proof_of_payment` longblob NOT NULL,
`payment_status` int(1) NOT NULL,
`remarks` varchar(50) NOT NULL,
`review_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_sms – this table will store the information of the sms in the system.

• api_code – unique API code for the sms
• api_password – the API password
• api_status – the API status
• message – the content of the sms

Create SQL Statement – the statement below is used to create the tbl_sms, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_sms` (
`api_code` varchar(50) NOT NULL,
`api_password` varchar(100) NOT NULL,
`api_status` int(1) NOT NULL,
`message` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_sms_logs – the sms logs information are stored in this table.

• sms_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• send_date – the date the message was sent
• member_id – this is a foreign key that points out to the member whom the message was sent
• message – the content of the message

Create SQL Statement – the statement below is used to create the tbl_sms_logs, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_sms_logs` (
`sms_id` int(11) NOT NULL,
`send_date` date NOT NULL,
`member_id` int(11) NOT NULL,
`messsage` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user – this table will store the information of the system users.

• user_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• fullname – the full name of the user
• avatar – this will hold the profile photo of the user
• username – the desired username of the user combined with the password to login to the system
• password – the preferred password of the user
• contact – the contact details of the user preferably mobile or telephone number
• email – the email address of the user
• user_group_id – this is a foreign key that points out to the user group
• status – (0)active, (1)inactive

Create SQL Statement – the statement below is used to create the tbl_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user` (
`user_id` int(11) NOT NULL,
`fullname` varchar(100) NOT NULL,
`avatar` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`user_group_id` int(11) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user_group – this table will store the information of the user group in the system.

• group_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• group_name – the name of the user group
• description – additional information about the user group
• allow_add – this column will allow adding information of the user group
• allow_edit – this column will allow editing of the user group information
• allow_delete – this column will allow deletion of user group information
• allow_print – this column will allow printing of user group information
• allow_import – this column will allow importing of user group information
• allow_export – this column will allow exporting user group information

Create SQL Statement – the statement below is used to create the tbl_user_group, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user_group` (
`group_id` int(11) NOT NULL,
`group_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`allow_add` int(1) NOT NULL,
`allow_edit` int(1) NOT NULL,
`allow_delete` int(1) NOT NULL,
`allow_print` int(1) NOT NULL,
`allow_import` int(1) NOT NULL,
`allow_export` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Video Tutorial

Summary

As a result of this post, we have compiled a list of tables that should be considered for inclusion in the creation of a project for a loan management system. The project includes the integration of SMS in the project. This will allow users to send and receive text messages from the project. Database design planning is significant because it assists in ensuring that the database is created in a way that satisfies the demands of the intended users of the database. The database designer may ensure that the database is easy to use and that the data is structured in a logical manner by planning the design of the database. This can help to increase the efficiency of the users’ work while also lowering the likelihood of data errors occurring.

Please watch the video tutorial on how to prepare and create the different tables of the database of Multi-branch Travel Agency and Booking System.

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive  free capstone projects resources and computer programming tutorials.

Hire our team to do the project.

Related Topics and Articles:

Loan Management System with SMS Free Bootstrap Template

Mobile Loan Management and Inquiry Application Capstone Project

Abstract of Mobile Loan Management Application with Admin Panel

Online Loan Management System with SMS Notification

Loan Transaction Monitoring with SMS Related Literature

Web Based Information System on Loan Management with SMS Notification

Loan Application and Information Management System in PHP and MySQL