Financial Management for Cooperative Database Project

Financial Management for Cooperative Database Project

Introduction

The world is changing, and electronic data storage has taken over paper-based traditional storage. Every day, more and more companies are switching to email or other electronic formats for documents. Emailing files can be an easy workaround for having files on the go without carrying bulky laptops around. But with this transition comes new risks in file management system management, which brings back to paper filing systems or other forms of physical documentation.

One way to keep up with your emails is by using SMS notifications. With SMS notification, you will alert your phone when someone sends you a new message through the company’s email server address that you registered.

Financial management has been used as a way for businesses to keep track of their money for years now. With the proliferation of smartphones and tablets, it has become even more straightforward for companies to use SMS notification as part of their financial management system so employees can keep track from anywhere enables them to be distracted from other tasks.

This article will provide you with a list of tables, as well as the fields and columns for each table, in the creation of the database structure/schema for a financial management system with SMS notification. The team will eventually provide a video instruction on how to build a database with PHPMyAdmin, which will be available on YouTube.

Database Tables

Let’s start by making the tables and columns. For a thorough tutorial, please watch the video.

tbl_barangay – this table will store the information of the barangay registered in the system.

• barangay_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).
• barangay_name – the name of the barangay

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

CREATE TABLE `tbl_barangay` (
`barangay_id` int(11) NOT NULL,
`barangay_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_member – the member’s information are held in this table.

• 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).
• barangay_id – this is a foreign key that points out to which barangay is the member is.
• member_code – this is a unique code given to a specific member.
• last_name – the last name of the member
• first_name – the first name of the member
• middle_name- the middle name of the member
• home_address- the address of the member’s home
• farm_address – the address of the member’s farm.
• contact – contact number of the member (mobile or cellphone number)
• email – email address of the member
• gender – the gender identity of the member
• birthdate – the date of birth of the member
• profile_picture – the display or profile picture of the member
• proof_of_identity- a photo or proof the member’s identity.
• is_active – the status of the member’s account
• user_id – a foreign key that points out to the member user.

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,
`barangay_id` int(11) NOT NULL,
`member_code` varchar(15) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`home_address` text NOT NULL,
`farm_address` text NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`gender` int(1) NOT NULL,
`birthdate` date NOT NULL,
`profile_picture` longblob NOT NULL,
`proof_of_identity` longblob NOT NULL,
`is_active` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_barangay_officer – this table store the information of the barangay officers registered in the system.

• officer_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).
• member_id – this is a foreign key that points out to the member
• position – the name of the barangay officer’s position.
• inclusion_date- the date of the barangay officer’s inclusion.
• is_active – the status of the barangay officer’s account in the system.
• user_id – this is a foreign key that points out to the barangay officer user.

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

CREATE TABLE `tbl_barangay_officer` (
`officer_id` int(11) NOT NULL,
`member_id` int(11) NOT NULL,
`position` varchar(30) NOT NULL,
`inclusion_date` varchar(30) NOT NULL,
`is_active` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_event_category – the details of the event category is stored in this table.

• category_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).
• event_name – the name of the event

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

CREATE TABLE `tbl_event_category` (
`category_id` int(11) NOT NULL,
`event_name` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_event – this table will store the information of the events to be conducted.

• event_id – this is the primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• event_category_id – this is a foreign key that points out to the event’s category.
• event_name – the name of the event
• description – additional information or description of the event
• meeting_link – the online link for the event
• event_date – the date of the event
• event_time- the time the event will be held
• user_id – this is a foreign key that points out to the user.

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

CREATE TABLE `tbl_event` (
`event_id` int(11) NOT NULL,
`event_category_id` int(11) NOT NULL,
`event_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`event_date` date NOT NULL,
`meeting_link` varchar(50) NOT NULL,
`event_time` time NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_file_category –  this table will store the information of the file category in the system.

• file_category_id -this is the primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• category_name – the name of the file category
• description – description of the file category

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

CREATE TABLE `tbl_file_category` (
`file_category_id` int(11) NOT NULL,
`category_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_file_management – this table will store the information of the files to be managed.

• file_id – this is the primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• file_category_id – this is a foreign key that points out to the faile category of the file
• file_name – name of the file
• file_description – description of the file
• file_upload – button to upload file
• updated_date – the date the file was updated.
• user_id – this is a foreign key that points out to the user.

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

CREATE TABLE `tbl_file_management` (
`file_id` int(11) NOT NULL,
`file_category_id` int(11) NOT NULL,
`file_name` varchar(50) NOT NULL,
`file_description` varchar(100) NOT NULL,
`file_upload` longblob NOT NULL,
`updated_date` date NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_expense –the details of the expenses will be held in this table.

• expense_id – this is the primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• reference_number – unique number that refer to a specific expense
• expense_name – name of the expense
• expense_description – the description of the expenses
• amount – the amoun of the expense
• date- the date when the expense is made
• user_id – this is a foreign key that points out to the user.

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

CREATE TABLE `tbl_expense` (
`expense_id` int(11) NOT NULL,
`reference_number` varchar(15) NOT NULL,
`expense_name` varchar(30) NOT NULL,
`expense_description` varchar(100) NOT NULL,
`amount` float NOT NULL,
`date` date NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_payment – this table store the information of the payment in the system.

• payment_id – this is the primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• reference_number – unique reference number for a specific payment.
• or_no – this is the unique number of the official receipt.
• amount – the amount made for the payment
• member_id – this is a foreign key that points out to the member.
• payment_date – the date when the payment was made.
• user_id – this is a foreign key that points out to the user.

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

CREATE TABLE `tbl_payment` (
`payment_id` int(11) NOT NULL,
`reference_number` varchar(15) NOT NULL,
`or_no` varchar(15) NOT NULL,
`amount` float NOT NULL,
`member_id` int(11) NOT NULL,
`payment_date` date NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_ledger – this is a table that hold the details reflected in the ledger.

• ledger_id – this is the primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• ledger_name – the name of the ledger
• control_number – unique control number of the ledger
• date – date the ledger was made
• total_expense – the total expenses recorded in the ledger
• total_payment- the total payment recorded in the ledger

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

CREATE TABLE `tbl_ledger` (
`ledger_id` int(11) NOT NULL,
`ledger_name` varchar(30) NOT NULL,
`control_number` varchar(15) NOT NULL,
`date` date NOT NULL,
`total_expense` float NOT NULL,
`total_payment` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_company_setting – this table store the information of the company.

• setting_id – this is the primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• company_name – the name of the company
• company_address – address of the company
• company_contact – contact number of the company
• company_tin – tin number of the company
• company_logo – the company’s logo
• user_id – this is a foreign key that points out to the user

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

CREATE TABLE `tbl_company_setting` (
`setting_id` int(11) NOT NULL,
`company_name` varchar(30) NOT NULL,
`company_address` text NOT NULL,
`company_contact` varchar(15) NOT NULL,
`company_tin` varchar(15) NOT NULL,
`company_logo` longblob NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user – the user’s information will be stored in this table.

• user_id – this is the  primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• username – the desired username of the user.
• password- the desired password of the user.
• avatar- the preferred avatar of the user for his/her account.
• fullname – the complete of the user.
• contact- the contact number of the user.
• email – the email address of the user.
• category – the category type of the user.
• status – the status of the users account. Ethier active or 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,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`avatar` longblob NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`category` int(1) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_sms – this table hold the information of the sms made in the system.

• sms_id – this is the  primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• api_code – unique api code
• api_password- the api password

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` (
`sms_id` int(11) NOT NULL,
`api_code` text NOT NULL,
`api_password` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_backup – this table store the information of the database backup.

• backup_id – this is the primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• backup_name – the name of the backup database
• backup_date – the date the backup database was made.

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

CREATE TABLE `tbl_backup` (
`backup_id` int(11) NOT NULL,
`backup_name` varchar(30) NOT NULL,
`backup_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

FREE DOWNLOAD SQL FILE

Summary

We live in a world where financial data is only a few taps away. By understanding how to manage finances, you can be confident that your money will “work” for you. If you’re looking for the right solution to help with day-to-day money management, then SMS notification and database systems might be perfect for you!

We hope that this post has provided you with a better understanding of the different tables used in financial management system and how they might assist your company.

Readers are also interested in:

Financial Management System Free Bootstrap Template

Top 94 Capstone Project Ideas with Related Literature

85 Best Management System Project Ideas

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.