Person with Disability Information System Database Design

Person with Disability Information System Database Design

The capstone project entitled “Person with Disability Information System” is an online platform that connects the records of the barangay to the city management office of person with disability. The core feature of the system is the record keeping and archiving of pwd profile per barangay. The said system has also a centralized notification module that allows the management to post updates, meetings and important news related to the pwd transactions.

This article will provide you with the list of tables and field/columns for every table in the design of database structure/schema of person with disability information system. The team will later provide a video tutorial on how to create the database in PHPMyAdmin.

This database design has 8 tables with their respective fields and columns as well as their relationships among each other.

Person with Disability Information System Database Design - List of Tables — Person with Disability Information System Database Design – List of Tables

tbl_brgy – the table will store the list of barangay registered in the city. It has the following fields/columns:

brgy_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 complete name of the barangay.
brgy_information – important and relevant information about the barangay.
user_id – the user who encode/manage/update the barangay information. It is a foreign key that connects to the user table.

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

CREATE TABLE `tbl_brgy` (
`brgy_id` int(11) NOT NULL,
`barangay_name` varchar(100) NOT NULL,
`brgy_information` varchar(200) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_blood_type – list of blood types are stored in this table, it has 3 columns.

blood_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).
blood_type – the type of the blood (A, B, A+, etc)
blood_type_info – important and relevant information about the bloodtype.

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

CREATE TABLE `tbl_blood_type` (
`blood_type_id` int(11) NOT NULL,
`blood_type` varchar(5) NOT NULL,
`blood_type_info` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_disability_type – list of disability type are stored in this table, it has 3 columns.

disability_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).
disability_type_name – name of the disability type.
description – important and relevant information about the disability type.

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

CREATE TABLE `tbl_disability_type` (
`disability_type_id` int(11) NOT NULL,
`disability_type_name` varchar(50) NOT NULL,
`description` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_pwd_info – personal profile of the person with disability will be stored in this table, the table has 21 columns.

pwd_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).
pwd_city_code_no – a unique number provided by the system for every pwd.
brgy_id – the registered barangay of the pwd. This is a foreign key that links to the tbl_brgy.
last_name – last name of the pwd.
first_name – first name of the pwd.
middle_name – middle name of the pwd.
complete_address – complete home address of the pwd.
contact_no – mobile number of the pwd.
birth_date – date of birth.
birth_place – place of birth of the pwd.
blood_type_id – blood type of the pwd, foreign key that connects to the tbl_blood_type.
disability_type_id – disability type of the pwd, foreign key that connects to the tbl_disability_type.
phil_health – philhealth number of pwd.
gender – 0 for male, 1 for female and 2 for others.
employment_type – 0 for none, 1 for private, 2 for government.
occupation – work/occupation of the pwd (if any).
emergency_contact_person – person to contact for emergency purposes.
relationship_to_contact_person – relationship to the contact person.
emergency_contact_address – address of the contact person.
profile_picture – 2×2 or passport size image of the pwd.
user_id – the user who encode/manage/update the pwd information. It is a foreign key that connects to the user table.

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

CREATE TABLE `tbl_pwd_info` (
`pwd_id` int(11) NOT NULL,
`pwd_city_code_no` varchar(15) NOT NULL,
`brgy_id` int(11) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`complete_address` varchar(100) NOT NULL,
`contact_no` varchar(15) NOT NULL,
`birth_date` date NOT NULL,
`birth_place` varchar(100) NOT NULL,
`blood_type_id` int(11) NOT NULL,
`disability_type_id` int(11) NOT NULL,
`phil_health` int(1) NOT NULL,
`gender` int(1) NOT NULL,
`employment_type` int(1) NOT NULL,
`occupation` varchar(30) NOT NULL,
`emergency_contact_person` varchar(15) NOT NULL,
`relationship_to_contact_person` varchar(15) NOT NULL,
`emergency_contact_address` varchar(15) NOT NULL,
`profile_picture` blob NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_notification – meetings, news, updates and other important information will be posted on this table. It has 5 columns

notification_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).
notification_name – name of the update/news/message.
notification_message – content of the message/notification.
notification_date – date of news/update/message/notification publication.
message_intended_to – recipient of the message.
user_id – the user who encode/manage/update the notification information. It is a foreign key that connects to the user table.

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

CREATE TABLE `tbl_notification` (
`notification_id` int(11) NOT NULL,
`notification_name` varchar(30) NOT NULL,
`notification_message` varchar(100) NOT NULL,
`notification_date` date NOT NULL,
`message_intended_to` int(11) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_services – this table will store the list of help, donation and services provided by the city, donors and barangay to the pwd. The table has 6 columns:

service_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).
service_name – name of the service (health assistance, relief goods, etc)
remarks – additional information on the services provided.
date – recorded date.
pwd_id – recipient of the service, this is a foreign key that links to the pwd table.
user_id – the user who encode/manage/update the services information. It is a foreign key that connects to the user table.

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

CREATE TABLE `tbl_services` (
`service_id` int(11) NOT NULL,
`service_name` varchar(30) NOT NULL,
`remarks` varchar(100) NOT NULL,
`date_recorded` date NOT NULL,
`pwd_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

tbl_user – this table will store the information of personnel who can access the records of the system.

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).
username – username of the personnel used to login together with the password.
password – password of the personnel used to login together with the username.
avatar – this will hold the profile image of the user.
fullname – the complete name of the personnel or user.
contact – contact number of the personnel (mobile/cellphone number).
email – email address of the personnel/user.
brgy_id – foreign key that links to the barangay table (this is the primary key value of tbl_brgy).
user_category_id – the user group or category of the user.
status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.

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` varchar(30) NOT NULL,
`avatar` blob NOT NULL,
`fullname` varchar(50) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(30) NOT NULL,
`brgy_id` int(11) NOT NULL,
`user_category_id` int(1) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user_group – this table store the information of the user group which includes the functions they can and can’t access in the system.

user_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 – name of the category or user group (brgy captain, brgy secretary, brgy encoder, administrator, city pwd officer, etc).
description – information on what the user group is all about.
allow_add – this column is to allow or prevent user from adding a record.
allow_edit – this column is to allow or prevent user from editing or updating a record.
allow_delete – this column is to allow or prevent user from removing or deleting a record.
allow_print – this column is to allow or prevent user from printing a report.
allow_import – this column is to allow or prevent user from importing records to the system.
allow_export – this column is to allow or prevent user from exporting records from the system.

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` (
`user_group_id` int(11) NOT NULL,
`group_name` varchar(30) NOT NULL,
`description` varchar(50) 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;

Indexes for dumped tables

--
-- Indexes for table `tbl_blood_type`
--
ALTER TABLE `tbl_blood_type`
ADD PRIMARY KEY (`blood_type_id`);

--
-- Indexes for table `tbl_brgy`
--
ALTER TABLE `tbl_brgy`
ADD PRIMARY KEY (`brgy_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_disability_type`
--
ALTER TABLE `tbl_disability_type`
ADD PRIMARY KEY (`disability_type_id`);

--
-- Indexes for table `tbl_notification`
--
ALTER TABLE `tbl_notification`
ADD PRIMARY KEY (`notification_id`),
ADD KEY `message_intended_to` (`message_intended_to`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_pwd_info`
--
ALTER TABLE `tbl_pwd_info`
ADD PRIMARY KEY (`pwd_id`),
ADD KEY `brgy_id` (`brgy_id`),
ADD KEY `blood_type_id` (`blood_type_id`),
ADD KEY `disability_type_id` (`disability_type_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_services`
--
ALTER TABLE `tbl_services`
ADD PRIMARY KEY (`service_id`),
ADD KEY `pwd_id` (`pwd_id`),
ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `tbl_user`
--
ALTER TABLE `tbl_user`
ADD PRIMARY KEY (`user_id`),
ADD KEY `user_category_id` (`user_category_id`),
ADD KEY `brgy_id` (`brgy_id`);

--
-- Indexes for table `tbl_user_group`
--
ALTER TABLE `tbl_user_group`
ADD PRIMARY KEY (`user_group_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_blood_type`
--
ALTER TABLE `tbl_blood_type`
MODIFY `blood_type_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_brgy`
--
ALTER TABLE `tbl_brgy`
MODIFY `brgy_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_disability_type`
--
ALTER TABLE `tbl_disability_type`
MODIFY `disability_type_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_notification`
--
ALTER TABLE `tbl_notification`
MODIFY `notification_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_pwd_info`
--
ALTER TABLE `tbl_pwd_info`
MODIFY `pwd_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_services`
--
ALTER TABLE `tbl_services`
MODIFY `service_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_user`
--
ALTER TABLE `tbl_user`
MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbl_user_group`
--
ALTER TABLE `tbl_user_group`
MODIFY `user_group_id` int(11) NOT NULL AUTO_INCREMENT;

Constraints for dumped tables

--
-- Constraints for table `tbl_brgy`
--
ALTER TABLE `tbl_brgy`
ADD CONSTRAINT `tbl_brgy_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_notification`
--
ALTER TABLE `tbl_notification`
ADD CONSTRAINT `tbl_notification_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_notification_ibfk_2` FOREIGN KEY (`message_intended_to`) REFERENCES `tbl_brgy` (`brgy_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_pwd_info`
--
ALTER TABLE `tbl_pwd_info`
ADD CONSTRAINT `tbl_pwd_info_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_pwd_info_ibfk_2` FOREIGN KEY (`brgy_id`) REFERENCES `tbl_brgy` (`brgy_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_pwd_info_ibfk_3` FOREIGN KEY (`blood_type_id`) REFERENCES `tbl_blood_type` (`blood_type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_pwd_info_ibfk_4` FOREIGN KEY (`disability_type_id`) REFERENCES `tbl_disability_type` (`disability_type_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_services`
--
ALTER TABLE `tbl_services`
ADD CONSTRAINT `tbl_services_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_services_ibfk_2` FOREIGN KEY (`pwd_id`) REFERENCES `tbl_pwd_info` (`pwd_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tbl_user`
--
ALTER TABLE `tbl_user`
ADD CONSTRAINT `tbl_user_ibfk_1` FOREIGN KEY (`user_category_id`) REFERENCES `tbl_user_group` (`user_group_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_user_ibfk_2` FOREIGN KEY (`brgy_id`) REFERENCES `tbl_brgy` (`brgy_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;