Vaccine Distribution System Free Database Design Tutorial

Vaccine Distribution System Free Database Design Tutorial

Introduction

The vaccine distribution system is the means through which vaccinations are distributed to the intended recipients. The system consists of a number of components, including producers, distributors, health care professionals, and patients. The system is meant to ensure that vaccines are delivered to the correct place, at the right time, and in the right condition. The system will also provide a database to help the government, health institutions and medical practitioners in maintaining records of vaccination.

Benefits of Vaccine Distribution System

When it comes to using the online vaccination distribution system, there are numerous advantages. Some of these advantages are as follows:

1. More Efficient and Accurate Vaccine Distribution – The online vaccine delivery method contributes to greater efficiency and accuracy in vaccine distribution. This is due to the fact that it contributes to the automation of the process and helps to ensure that the relevant vaccines are given to the appropriate locations.
2. It is a more secure system – The online vaccine distribution system makes use of encrypted communication and only enables access to individuals who are in need of the vaccination. Because of this, the Vaccine Distribution Center will be able to provide vaccines with greater accuracy, hence increasing the likelihood of delivering vaccines to individuals who are most in need of them.
3. Cost-cutting measures – The online vaccination distribution system can assist in drastically lowering the costs of operation. This is due to the fact that it helps to ensure that vaccines are given straight from the manufacturer to those who require them.

Database Tables

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

tbl_registration  – this table will hold the registration information of the system’s users.

• registration_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 first name of the user
• last_name – the last name of the user
• middle_name – the middle name of the user
• contact – the contact number of the user, preferrably mobile or cellphone number
• email_address – the user’s email address
• date_of_birth – the user’s date of birth (month,day, year)
• username – the desired username of the user for his/her account, this is one of the requirement to login to the system
• password – the preferred password of the user for his/or account, combine with the username to login to the system
• status – pending, approved, disapproved

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

CREATE TABLE `tbl_registration` (
`registration_id` int(11) NOT NULL,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`contact` varchar(15) NOT NULL,
`email_address` varchar(30) NOT NULL,
`date_of_birth` date NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_vaccination_center – the details of the vaccination centers will be store in this table.

• center_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).
• vaccination_center – the name of the vaccination center
• address – the address where the vaccination center is located
• contact_number – the contact number of the vaccination center, can be a telephone or a mobile number
• longitude – the longitude measurement of the vaccination center’s location
• latitude – the latitude measurement of the vaccination center’s location
• status – open, closed

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

CREATE TABLE `tbl_vaccination_center` (
`center_id` int(11) NOT NULL,
`vaccination_center` varchar(30) NOT NULL,
`address` varchar(100) NOT NULL,
`contact_number` varchar(15) NOT NULL,
`longitude` varchar(15) NOT NULL,
`latitude` varchar(15) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_front_liner_category –  this table will hold the information of the front liner’s category in the system.

• 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).
• category_name – the front liner’s category name
• job_description – description of the front liner’s job

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

CREATE TABLE `tbl_front_liner_category` (
`category_id` int(11) NOT NULL,
`category_name` varchar(15) NOT NULL,
`job_description` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_front_liner_info – this table will store the front liner’s personal information in the system.

• front_liner_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).
• complete_name – the complete name of the front liner
• front_liner_category_id – this is a foreign key that points put to the front liner’s category
• contact_number – the contact number of the front liner, preferrably mobile or cellphone number
• email_address – the email address of the front liner
• facebook_account – the username of the front liner’s facebook account

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

CREATE TABLE `tbl_front_liner_info` (
`front_liner_id` int(11) NOT NULL,
`complete_name` varchar(50) NOT NULL,
`front_liner_category_id` int(11) NOT NULL,
`contact_number` varchar(15) NOT NULL,
`email_address` varchar(30) NOT NULL,
`facebook_account` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_vaccine_info – the vaccine’s information will hold and stored in this table.

• vaccine_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).
• vaccine_name – the name of the vaccine
• vaccine_info – additional information about the vaccine

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

CREATE TABLE `tbl_vaccine_info` (
`vaccine_id` int(11) NOT NULL,
`vaccine_name` varchar(30) NOT NULL,
`vaccine_info` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_vaccination_schedule – this table will hold the information of the vaccination schedule.

• schedule_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).
• date – the scheduled date for vaccination
• time_start – the time the vaccination will start
• time_end – the time the vaccination will end
• vaccination_center_id – this is a foreign key that points out to the vaccination center
• slots_available – the number of slots available for vaccination
• vaccine_id – this is a foreign key that points out to the vaccine that will be administered

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

CREATE TABLE `tbl_vaccination_schedule` (
`schedule_id` int(11) NOT NULL,
`date` date NOT NULL,
`time_start` time NOT NULL,
`time_end` time NOT NULL,
`vaccination_center_id` int(11) NOT NULL,
`slots_available` int(5) NOT NULL,
`vaccine_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_vaccine_record –  the vaccine records are held and stored in this table.

• record_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).
• registration_id – this is a foreign key that points out to the registration of the vaccinee
• vaccine_id – this is a foreign key that points out to the vaccine used
• date_of_1_dose – the date the first dose was administered
• date_of_2_dose – datewhen was the 2^nd dose was administered
• date_booster_shot – the date the booster shot was administered
• vaccine_booster – the name of the vaccine booster
• front_liner_id – this is a foreign key that points to the front liner
• doctor_comments – comments of the doctor for the vaccination
• feeling_of_patient_before_vaccination – the details of the patient’s screening before vaccination
• remarks – additional information for the vaccine record

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

CREATE TABLE `tbl_vaccine_record` (
`record_id` int(11) NOT NULL,
`registration_id` int(11) NOT NULL,
`vaccine_id` int(11) NOT NULL,
`date_of_1_dose` date NOT NULL,
`date_of_2_dose` date NOT NULL,
`date_booster_shot` date NOT NULL,
`vaccine_booster` int(11) NOT NULL,
`front_liner_id` int(11) NOT NULL,
`doctor_comments` varchar(50) NOT NULL,
`feeling_of_patient_before_vaccination` varchar(30) NOT NULL,
`remarks` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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.
• 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` text NOT NULL,
`avatar` blob NOT NULL,
`fullname` varchar(100) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`user_category_id` int(11) 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 (this field will group the user based on their designation).
• 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(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;

Summary

The lists of tables above are the components of our database for the project entitled Vaccine Distribution System. Feel free to modify the database based on your requirements.

Please watch the video tutorial that will be posted on our YouTube Channel.

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.

Vaccine Distribution System Bootstrap Template

QR Code Digital Vaccine Certification Capstone Project

COVID-19 Capstone and Research Free Project Ideas 2022

Online Birth Certificate Processing System with SMS Notification Free Bootstrap Template

Capstone Project Ideas for IT and IS January 2022