Barangay Management System Database Design

January 21, 2020July 17, 2020 inettutor.com

Barangay Management System Database Design

The barangay management system is an information system that will automate the records of barangay in the profiling of their inhabitant and other transactions as well.

This article is actually our database design for our barangay management system developed in Visual Basic and PHP, MySQL.

Barangay Management System Database Tables

Database Design/Schema/Tables

tblactivity (id, date_of_activity, activity, description, user_id)

Description: the tblactivity table will store the different events and activities of the barangay for the whole year. The table has four entities; id is the primary key, date_of_activity refers to the date the activity will be held, the name of the activity and the detailed description of the event.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblactivity` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dateofactivity` date NOT NULL,
`activity` text NOT NULL,
`description` text NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblactivityphoto (id, activity_id, filename)

Description: the system can accept multiple banners and images for a certain activity, those images will be stored in the tblactivityphoto.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblactivityphoto` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`activity_id` int(11) NOT NULL,
`filename` text NOT NULL,
PRIMARY KEY (`id`),
KEY `activityid` (`activity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblblotter (id, year_recorded, date_recorded, complainant, c_age, c_address, c_contact, person_to_complain, p_age, p_address, p_contact, complaint, action_taken, complaint_status, location_of_incidence, user_id)

Description:

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblblotter` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`year_recorded` varchar(4) NOT NULL,
`date_recorded` date NOT NULL,
`complainant` varchar(50) NOT NULL,
`c_age` int(3) NOT NULL,
`c_address` varchar(100) NOT NULL,
`c_contact` varchar(11) NOT NULL,
`person_to_complain` varchar(50) NOT NULL,
`p_age` int(3) NOT NULL,
`p_address` varchar(100) NOT NULL,
`p_contact` int(11) NOT NULL,
`complaint` varchar(100) NOT NULL,
`action_taken` varchar(50) NOT NULL,
`complaint_status` varchar(50) NOT NULL,
`location_of_incidence` varchar(50) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblclearance (id, clearance_no, resident_id, findings, purpose, or_no, amount, date_recorded, user_id, status)

Description: barangay clearance is one the government issued identification documents needed for many important business, job, or personal transactions. Every time a resident claim or process the clearance, the system stores the information in the tblclearance table. The table has 10 fields or columns; the primary key (id), the clearance number for control

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblclearance` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`clearance_no` int(11) NOT NULL,
`resident_id` int(11) NOT NULL,
`findings` varchar(50) NOT NULL,
`purpose` varchar(50) NOT NULL,
`or_no` varchar(11) NOT NULL,
`amount` float NOT NULL,
`date_recorded` date NOT NULL,
`user_id` int(11) NOT NULL,
`status` varchar(15) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `resident_id` (`resident_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblhousehold (id, household_no, zone_id, household_member, head_of_family)

Description: according to the website https://legal-dictionary.thefreedictionary.com/household household is defined as individuals who comprise a family unit and who live together under the same roof; individuals who dwell in the same place and comprise a family, sometimes encompassing domestic help; all those who are under the control of one domestic head. The tblhousehold contains 5 entities or fields; the primary key (id), household_no is the field where the control number of the household is stored, zone_id is the foreign that links to the tblzone, household_member represents the number of individuals that resides in the family, and the head_of_family usually the represented by the father of the family.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblhousehold` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`household_no` int(5) NOT NULL,
`zone_id` int(11) NOT NULL,
`household_member` int(2) NOT NULL,
`head_of_family` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `zone_id` (`zone_id`,`head_of_family`),
KEY `head_of_family` (`head_of_family`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbllog (id, user_id, log_date, action)

Description: the tbllog stores the activities of the users in the system such as the adding of new items, updating of records and the login time and date.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbllogs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`log_date` datetime NOT NULL,
`action` text NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblofficial (id, position, complete_name, contact, address, term_start, term_end, status, user_id)

Description: tblofficial is the table that stores the information of the barangay official, the table includes the position, complete name, contact, address, the term started and the term ended.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblofficial` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`position` varchar(50) NOT NULL,
`complete_name` varchar(50) NOT NULL,
`contact` varchar(20) NOT NULL,
`address` varchar(100) NOT NULL,
`term_start` date NOT NULL,
`term_end` date NOT NULL,
`status` varchar(20) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpermit (id, resident_id, business_name, business_address, type_of_business, or_no, amount, date_recorded, user_id, status)

Description: a permit is an official documentation that gives someone the authorization to do something. In this case, the permit refers to the business permit which will allow the individual or group of persons to open a business in the area of barangay. The database table to store the information is the tblpermit table. The information includes the id which is the primary key, the resident_id that links to the information of resident in the tblresident table, the business name, address, type of business, the official receipt number issued in the barangay/city, the amount paid, date of payment, the staff who encoded the transaction in the form of user_id which connects to the tbluser table and the status of the application or permit.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblpermit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`resident_id` int(11) NOT NULL,
`business_name` varchar(50) NOT NULL,
`business_address` varchar(100) NOT NULL,
`type_of_business` varchar(50) NOT NULL,
`or_no` varchar(15) NOT NULL,
`amount` float NOT NULL,
`date_recorded` date NOT NULL,
`user_id` int(11) NOT NULL,
`status` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `resident_id` (`resident_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblresident (id, last_name, first_name, middle_name, birth_date, birth_place, age, zone_id, total_household, differentlyabledperson, relationto_head_of_family, marital_status, blood_type, civil_status, occupation, monthly_income, household_no, length_of_stay, religion, nationality, gender, skills, philhealth_no, highest_educational_attainment, house_ownership_status, land_ownership_status, dwelling_type, water_usage, lightning_facilities, sanitary_toilet, former_address, remarks, image, username, password)

Description: all information that can be extracted from the residents will be encoded in the system; it will be stored in the tblresident database table. The resident can also access the system for the online request of clearance and business permit.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblresident` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`last_name` varchar(20) NOT NULL,
`first_name` varchar(20) NOT NULL,
`middle_name` varchar(20) NOT NULL,
`birth_date` date NOT NULL,
`birth_place` varchar(100) NOT NULL,
`age` int(3) NOT NULL,
`zone_id` int(11) NOT NULL,
`total_household` int(3) NOT NULL,
`differentlyabledperson` varchar(100) NOT NULL,
`relationto_head_of_family` varchar(15) NOT NULL,
`marital_status` int(1) NOT NULL,
`blood_type` int(1) NOT NULL,
`civil_status` int(1) NOT NULL,
`occupation` varchar(100) NOT NULL,
`monthly_income` float NOT NULL,
`household_no` int(11) NOT NULL,
`length_of_stay` varchar(15) NOT NULL,
`religion` int(1) NOT NULL,
`nationality` varchar(30) NOT NULL,
`gender` int(1) NOT NULL,
`skills` varchar(100) NOT NULL,
`philhealth_no` varchar(15) NOT NULL,
`highest_educational_attainment` int(1) NOT NULL,
`house_ownership_status` int(1) NOT NULL,
`land_ownership_status` int(1) NOT NULL,
`dwelling_type` int(1) NOT NULL,
`water_usage` int(1) NOT NULL,
`lightning_facilities` int(1) NOT NULL,
`sanitary_toilet` int(1) NOT NULL,
`former_address` varchar(1) NOT NULL,
`remarks` varchar(100) NOT NULL,
`image` text NOT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `zone_id` (`zone_id`,`household_no`),
KEY `zone_id_2` (`zone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser (id, complete_name, username, password, type)

Description: staff and administrator shares the same database table, it stores the user account information in the tbluser.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbluser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`complete_name` varchar(50) NOT NULL,
`username` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`type` int(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblzone (id, zone, username, password, user_id)

Description: zones or puroks are stored in the separate table which makes the system more dynamic. The administrator can encode the list of zones and puroks in the system.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblzone` (
`id` int(11) NOT NULL,
`zone` varchar(15) NOT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Barangay Management System Database Design

Constraints for dumped tables

—
— Constraints for table `tblactivity`
—
ALTER TABLE `tblactivity`
ADD CONSTRAINT `tblactivity_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblactivityphoto`
—
ALTER TABLE `tblactivityphoto`
ADD CONSTRAINT `tblactivityphoto_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `tblactivity` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblblotter`
—
ALTER TABLE `tblblotter`
ADD CONSTRAINT `tblblotter_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblclearance`
—
ALTER TABLE `tblclearance`
ADD CONSTRAINT `tblclearance_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblclearance_ibfk_1` FOREIGN KEY (`resident_id`) REFERENCES `tblresident` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblhousehold`
—
ALTER TABLE `tblhousehold`
ADD CONSTRAINT `tblhousehold_ibfk_2` FOREIGN KEY (`zone_id`) REFERENCES `tblzone` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblhousehold_ibfk_1` FOREIGN KEY (`head_of_family`) REFERENCES `tblresident` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbllogs`
—
ALTER TABLE `tbllogs`
ADD CONSTRAINT `tbllogs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblofficial`
—
ALTER TABLE `tblofficial`
ADD CONSTRAINT `tblofficial_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblpermit`
—
ALTER TABLE `tblpermit`
ADD CONSTRAINT `tblpermit_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpermit_ibfk_1` FOREIGN KEY (`resident_id`) REFERENCES `tblresident` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblresident`
—
ALTER TABLE `tblresident`
ADD CONSTRAINT `tblresident_ibfk_1` FOREIGN KEY (`zone_id`) REFERENCES `tblzone` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Free Download .sql file

You may visit our facebook page for more information, inquiries and comments.

Hire our team to do the project.

capstone project, capstone project ideas, capstone project in information technology, capstone project in it, capstone project sample for information technology, capstone project title in information technology, capstone proposal in it, free download capstone project in information technology, free download capstone project source code, it capstone project, it capstone sample documentation, it project, it thesis proposal, project design management system proposal, sample thesis documentation in information technology, system analysis and design proposal presentation, systemdevelopment, thesis documentation for information technology, thesis documentation in informationtechnology, thesis titles in information technology, thesis titles in it, title proposal for system analysis and design

▲