Barangay Management System Development Part 2

Barangay Management System Development Part 2 – Database Design

This is the part two or our tutorial on how to develop a barangay management system. The part 2 of the tutorial is all about the database design or model of the system. It was stated on the first part of the tutorial that we will use Visual Basic and MySQL for the development of the barangay management system.

We need to install first the XAMPP installer that includes the mysql server. The barangay management system that we will be creating consists of 6 tables, and they will be explained one by one including their fields and entities. The article will also include the create sql statement for you to be able to use in order to create and establish the database tables.

Barangay Management System Development Part 2 - Database Design — Barangay Management System Development Part 2 – Database Design

After you have successfully installed the XAMPP, you need to make sure that the apache and mysql service are up and running. Next is to setup our database using the PHPMyAdmin tool, it is a graphical user interface used to access and navigate to our database server. We will name our database as BarangaySysDB.

tbl_UserAccounts – the user account table will store and manage the information of persons who can access the records of the barangay. The users can add, update and search for information and the table has 7 fields or columns.

user_id – primary key of the table and is set to be incremented by one for every recorded item.
full_name – this will store the full name of the user.
position – this refers to the job position of the user in the barangay.
contact – contact information of the user, preferably the cellphone number.
address – complete address of the user.
username – the desired username of the user.
password – the desired password of the user.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tbl_useraccounts` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`full_name` varchar(50) NOT NULL,
`position` int(1) NOT NULL,
`contact` varchar(15) NOT NULL,
`address` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbl_Purok – purok are the sub-unit in the barangay system. Lists of puroks will be encoded and stored in this table and it has 3 entities.

purok_id – primary key of the table and is set to be incremented by one for every recorded item.
purok_name – this is the column that stores the name of the purok.
remarks – a brief description or you can input the history of the purok, although this field is optional.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tbl_purok` (
`purok_id` int(11) NOT NULL AUTO_INCREMENT,
`purok_name` varchar(30) NOT NULL,
`remarks` varchar(50) NOT NULL,
PRIMARY KEY (`purok_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbl_PurokPresident – every purok has also its own leader and the leader is called the purok leader or purok president. The purpose of this table is to store the information of the purok leader.

purokpres_id – primary key of the table and is set to be incremented by one for every recorded item.
resident_id – this is the foreign key of the table that links to the tbl_ResidentInfo. The main purpose of the foreign key is what we call the referential integrity of the records, it means that the record found on this table should point out to the record of the source or main table; otherwise we cannot establish a relationship.
term_start – the date it was elected as the purok president.
term_end – the last day of service of the purok president.
term_index – this is a concatenation of the year started and year ended.
set_current – this is the field that represents the current purok president.
remarks – significant information about the purok president.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tbl_purokpresident` (
`purokpres_id` int(11) NOT NULL AUTO_INCREMENT,
`purok_id` int(11) NOT NULL,
`resident_id` int(11) NOT NULL,
`term_start` date NOT NULL,
`term_end` date NOT NULL,
`term_index` varchar(15) NOT NULL,
`set_current` int(1) NOT NULL,
`remarks` varchar(50) NOT NULL,
PRIMARY KEY (`purokpres_id`),
KEY `resident_id` (`resident_id`),
KEY `purok_id` (`purok_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbl_ResidentInfo – this table will store the information of the resident. Based on our experience, every barangay has a different form used for the data gathering of their residents. This table can be customized and modified based on your requirements.

resident_id – primary key of the table and is set to be incremented by one for every recorded item.
last_name – this column will store the last name of the resident.
first_name – this column will store the first name of the resident.
middle_name – this column will store the middle name of the resident.
birth_date – it will store the date of birth of the resident.
age – it will store the age of the resident.
purok_id – this is the foreign key that links to the tbl_Purok.
civil_status – this column will store the civil status of the resident. To save database storage, we will assign numbers for every value, example: single is 1, married is 2.
religion – this field will store the religion of the resident.
gender – this field is for the gender of the resident.
complete_address – complete address of the resident.
contact_no – contact information of the resident (mobile, landline, etc).
with_philhealth – it will indicate if the resident is a member of philhealth.
with_sss – it will indicate if the resident is a member of social security system.
income – the gross income of the resident per month.
member_4ps – it will indicate if the resident is a member of 4Ps.
voter_status – it will indicate if the resident is a registered voter.
encoded_by – this is the foreign key that links to the tbl_UserAccounts, it will record the user who encoded the resident information.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tbl_residentinfo` (
`resident_id` int(11) NOT NULL AUTO_INCREMENT,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`birth_date` date NOT NULL,
`age` int(3) NOT NULL,
`purok_id` int(11) NOT NULL,
`civil_status` int(1) NOT NULL,
`religion` varchar(30) NOT NULL,
`gender` int(1) NOT NULL,
`complete_address` varchar(100) NOT NULL,
`contact_no` varchar(15) NOT NULL,
`with_philhealth` int(1) NOT NULL,
`with_sss` int(1) NOT NULL,
`income` float NOT NULL,
`member_4ps` int(1) NOT NULL,
`voter_status` int(1) NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`resident_id`),
KEY `purok_id` (`purok_id`,`encoded_by`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbl_BrgyOfficial – this is the table that will record, store and archive the information on barangay official.

official_id – primary key of the table and is set to be incremented by one for every recorded item.
resident_id – this is the foreign key that links to the tbl_ResidentInfo.
term_start – the date it was elected as a barangay official.
term_end – the last day of service of the official.
term_index – this is a concatenation of the year started and year ended.
set_current – this is the field that represents the current barangay official.
remarks – significant information about the barangay official.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tbl_brgyofficial` (
`official_id` int(11) NOT NULL AUTO_INCREMENT,
`resident_id` int(11) NOT NULL,
`term_start` date NOT NULL,
`term_end` date NOT NULL,
`term_index` varchar(15) NOT NULL,
`set_current` int(1) NOT NULL,
`remarks` varchar(100) NOT NULL,
PRIMARY KEY (`official_id`),
KEY `resident_id` (`resident_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbl_Clearance

clearance_id – primary key of the table and is set to be incremented by one for every recorded item.
resident_id – this is the foreign key that links to the tbl_ResidentInfo.
date_recorded – it refers to the date the clearance was printed.
or_no – OR number of the transaction.
amount – amount paid by the resident.
remarks – significant information about the transaction.
encoded_by – this is the foreign key that links to the tbl_UserAccounts.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tbl_clearance` (
`clearance_id` int(11) NOT NULL AUTO_INCREMENT,
`resident_id` int(11) NOT NULL,
`date_recorded` date NOT NULL,
`or_no` int(11) NOT NULL,
`amount` float NOT NULL,
`remarks` varchar(100) NOT NULL,
`encoded_by` int(11) NOT NULL,
PRIMARY KEY (`clearance_id`),
KEY `resident_id` (`resident_id`,`encoded_by`),
KEY `encoded_by` (`encoded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Barangay Management System Development Database Design

Constraints for dumped tables

--
-- Constraints for table `tbl_brgyofficial`
--
ALTER TABLE `tbl_brgyofficial`
ADD CONSTRAINT `tbl_brgyofficial_ibfk_1` FOREIGN KEY (`resident_id`) REFERENCES `tbl_residentinfo` (`resident_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_clearance`
--
ALTER TABLE `tbl_clearance`
ADD CONSTRAINT `tbl_clearance_ibfk_2` FOREIGN KEY (`resident_id`) REFERENCES `tbl_residentinfo` (`resident_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_clearance_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbl_useraccounts` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_purokpresident`
--
ALTER TABLE `tbl_purokpresident`
ADD CONSTRAINT `tbl_purokpresident_ibfk_2` FOREIGN KEY (`purok_id`) REFERENCES `tbl_purok` (`purok_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_purokpresident_ibfk_1` FOREIGN KEY (`resident_id`) REFERENCES `tbl_residentinfo` (`resident_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_residentinfo`
--
ALTER TABLE `tbl_residentinfo`
ADD CONSTRAINT `tbl_residentinfo_ibfk_2` FOREIGN KEY (`purok_id`) REFERENCES `tbl_purok` (`purok_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_residentinfo_ibfk_1` FOREIGN KEY (`encoded_by`) REFERENCES `tbl_useraccounts` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Database Free Download (.sql file)

You may also visit the following articles related to the barangay information system.

Barangay Records Management Features and User Interface

City Wide Barangay Management System in PHP and MySQL

see you on the part 3 of this free tutorial.

iNetTutor.com