Virtual and Remote Guidance Counselling System Database Design
About the System
Today, technology has been used to develop platforms that can digitally replicate real-life problems and improve the efficacy of dealing with them. The goal of the new existing invention in ICT is to provide a wide range of benefits to each individual in order to make their life easier and more productive. Circumstances cannot be avoided; thus, it is critical to be prepared for any situation in which we can analyze and accomplish the task, regardless of how awful the situation is. As a result of today’s events, a pandemic has struck many people’s life, halting all sessions and physical contact. In this type of environment, the demand for counseling is growing since people are more likely to rot at home, causing them to make mistakes and experience other events that require counseling. However, because people are barred from making physical contact under the regulations that must be followed, it will be difficult for individuals to seek counseling. It is for this reason that the capstone project “Virtual and Remote Guidance Counselling System” was implemented, which fills the gap in providing counseling in difficult situations.
A well-designed database is critical for the efficient and accurate handling of information. A well-designed database will reduce the need for data entry and data correction, and it will aid in ensuring that data is easily accessible and usable by all parties. A well-designed database also makes data analysis and reporting easier to do. Data redundancy and inconsistency are common problems with poorly constructed databases, which make it difficult to manage and utilise the information stored within. An efficiently built database is more efficient and easier to use than an inefficiently designed database, in general.
Database Tables
Let’s start by making the tables and columns. For a thorough tutorial, please watch the video.
tbl_referral_reason – this table will store the information of the reasons as to why the student is referred to guidance counselling.
- reason_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).
- reason – the reason of the referral
- description – further description about the reason of the referral.
Create SQL Statement – the statement below is used to create the tbl_referral_reason, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_referral_reason` ( `reason_id` int(11) NOT NULL, `reason` varchar(50) NOT NULL, `description` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_course – the details of the courses will be store in this table in the system.
- course_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).
- course_code – a unique code given for a specific course.
- complete_name – the name of the course
Create SQL Statement – the statement below is used to create the tbl_course, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_course` ( `course_id` int(11) NOT NULL, `course_code` varchar(15) NOT NULL, `complete_name` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_student – this table will store the information of the student in the system.
- student_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).
- last_name – the student’s last name
- first_name – the student’s first name
- middle_name – the student’s middle name
- course_id -this is a foreign key that points out to the course
- age – the age of the student
- gender- the gender of the student
- username – the desired user name of the student for his/her account
- password – the desired password of the student for his/her account
- account_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_student, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_student` ( `student_id` int(11) NOT NULL, `last_name` varchar(30) NOT NULL, `first_name` varchar(30) NOT NULL, `middle_name` varchar(30) NOT NULL, `course_id` int(11) NOT NULL, `age` int(3) NOT NULL, `gender` int(1) NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `account_status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_appointment_schedule – this table will store the information of the appointment schedule for counselling.
- 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 date of the appointment
- number_of_slots – slots available for the appointment schedule
Create SQL Statement – the statement below is used to create the tbl_appointment_schedule, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_appointment_schedule` ( `schedule_id` int(11) NOT NULL, `date` date NOT NULL, `number_of_slots` int(3) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_reservation – this table will store the information of the counselling reservation in the system.
- reservation_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).
- student_id- this is a foreign key that points out to the student
- referral_id – this is a foreign key that points out to the counselling referral
- concern -the reason for counselling
- date – the date for the counselling reservation
- time – the time for the counselling reservation
- meeting_link – the meeting link for the counselling
- status- this will show is the reservation was completed or not
- counsellor_id – this is a foreign key that points out to the counsellor
Create SQL Statement – the statement below is used to create the tbl_reservation, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_reservation` ( `reservation_id` int(11) NOT NULL, `student_id` int(11) NOT NULL, `referral_id` int(11) NOT NULL, `concern` varchar(50) NOT NULL, `date` date NOT NULL, `time` time NOT NULL, `meeting_link` varchar(50) NOT NULL, `status` int(1) NOT NULL, `counsellor_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_counselling – the counselling information will be stored in this table.
- counselling_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).
- student_id – this is a foreign key that points out to the student
- counsellor_message- the message of the counsellor
- strategies_advice – the advice given during the counselling
- video_recording_link – the link for the recorded video of the session
- remarks – additional information about the counselling
- date_time – the date and time of the counselling
- counsellor_id – this is a foreign key that points out to the counsellor
Create SQL Statement – the statement below is used to create the tbl_counselling, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_counselling` ( `counselling_id` int(11) NOT NULL, `student_id` int(11) NOT NULL, `counsellor_message` varchar(200) NOT NULL, `strategies_advice` varchar(200) NOT NULL, `video_recording_link` varchar(100) NOT NULL, `remarks` varchar(100) NOT NULL, `date_time` datetime NOT NULL, `counsellor_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_counsellor – this table will store the information of the counsellor in the system.
- counsellor_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 – complete name of the counsellor
- work_history – the work history of the counsellor
- username – the desired username of the counsellor
- password – the desired password of the counsellor, combined with the username to login to the system.
Create SQL Statement – the statement below is used to create the tbl_counsellor, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_counsellor` ( `counsellor_id` int(11) NOT NULL, `complete_name` varchar(100) NOT NULL, `work_history` text NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Summary
The main objective of this post is to give you with the idea and example on how to develop a database for the project on virtual and remote guidance counselling system. We hope that this tutorial has supplied you with information that might aid in creating and constructing your own version of the virtual and remote guidance counselling system or similar projects related to it.
People in need of counseling and guidance can access the Virtual and Remote Guidance Counselling System (VRGCS), which is a computer system that provides counseling and guidance services to them virtually and remotely. It is a web-based system that connects people who are in need with a skilled therapist through the use of a secure network. The VRGCS provides consumers with the ability to obtain counseling and guidance services from anywhere in the world. No matter what their financial or social level is, they are all welcome.
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.
Related Topics and Articles:
Virtual and Remote Guidance Counselling System Free Bootstrap and PHP Script
IPO Model Conceptual Framework of AdviseMobile A Web and Mobile Based Guidance Consultation System
AdviseMobile Web and Mobile Guidance Consultation System
