How to Create Database for Driving School Management System
Introduction
Driving School Management Systems are designed to make the task of managing your driving school easier. They are generally online applications that allow you to track students’ progress, track attendance, generate reports on student performance, and give instructors an easy way to grade their students.
This article will give you with the list of tables and field/columns for every table in the design of database structure/schema of driving school management system.
tbl_student – this table of the project has 13 columns. The purpose of this table is to hold records of the students profile and personal information.
- student_id – This column serves as the table’s primary key. Auto increment is the default setting in most circumstances (the database will automatically give this column a value starting from 1).
- last_name – This column will store the last name of the student.
- first_name – The student’s first name will be stored in this column.
- middle_name – This column will contain the student’s middle name.
- gender – This column will have the student’s gender.
- date_of_birth – This will store the birth date of the student.
- address – complete address of the student will recorded ad stored in this field.
- contact – contact number such as the phone or mobile number of the student.
- email_address – valid email address of the student.
- profile_picture – official photo of the student and it will also be used for identification card.
- username – desired username of student or in some cases, it is their student id number.
- password – password of the student will be generated first by the system and they can change it later.
- account_status – Accounts with admin or staff privileges can change a user’s status from active to inactive.
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, `gender` int(1) NOT NULL, `date_of_birth` date NOT NULL, `address` varchar(200) NOT NULL, `contact` varchar(15) NOT NULL, `email_address` varchar(50) NOT NULL, `profile_picture` text NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `account_status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_instructor – There are 14 columns in this project’s table. This table’s function is to keep track of the instructor’s profile and personal information.
- instructor_id – this is the primary key of the instructor table.
- last_name – The instructor’s last name will be stored in this column.
- first_name – This column will be used to contain the instructor’s last name.
- middle_name – This column will store the instructor’s middle name.
- gender
- date_of_birth – birth date of the instructor, this will also be used to verify the age of the instructor.
- address – complete address of the instructor.
- contact – The instructor’s contact information, such as his or her phone or cell number.
- email_address – email address of the instructor that is current and active
- driving_experience – this will store the professional driving experience of the instructor.
- profile_picture – It will be used for the instructor identification card as well as an official photo of the instructor.
- username – desired username of the instructor to access the system.
- password – The instructor’s password will be generated by the system, and they will have the option to modify it later.
- account_status – Administrator or staff accounts have the ability to modify a user’s status from active to inactive.
Create SQL Statement – the statement below is used to create the tbl_instructor, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_instructor` ( `instructor_id` int(11) NOT NULL, `last_name` varchar(30) NOT NULL, `first_name` varchar(30) NOT NULL, `middle_name` varchar(30) NOT NULL, `gender` int(1) NOT NULL, `date_of_birth` date NOT NULL, `address` varchar(200) NOT NULL, `contact` varchar(15) NOT NULL, `email_address` varchar(50) NOT NULL, `driving_experience` text NOT NULL, `profile_picture` text NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `account_status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_schedule – this table is responsible for storing the list of driving activities set by the instructor or the system’s user. Schedule table has 7 columns.
- schedule_id – this column serves as the primary key of the table.
- schedule_code – the value of this column will be generated automatically by the system.
- date – date of activity or driving lesson.
- instructor_id – this is the foreign key that represents the instructor who will handle the driving lesson.
- no_of_slots – this is the number of students allowed to take the driving lesson for a specific date.
- amount – amount to be paid by the students to take the driving lesson or assessment.
- remarks – additional information, messages or comments about the schedule.
Create SQL Statement – the statement below is used to create the tbl_schedule, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_schedule` ( `schedule_id` int(11) NOT NULL, `schedule_code` varchar(15) NOT NULL, `date` date NOT NULL, `instructor_id` int(11) NOT NULL, `no_of_slots` int(3) NOT NULL, `amount` float NOT NULL, `remarks` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_enrollment – The enrollment table will hold and store the records of students who want to take a driving lesson or activities. The said table has 6 columns
- enrollment_id – the first column of the table is usually the primary key of the table.
- schedule_id – This is a foreign key that links or connects to the schedule table. It represents the schedule selected by the student.
- student_id – This is another foreign key that links to the student table. This is the student who wants to take the driving lesson.
- instructor_id – The instructor who will handle the driving lesson. Another foreign key that connects to the instructor table.
- remarks – further information, remarks, or comments regarding the driving lesson.
- user_id – processing officer, a foreign key that connects to the user table.
Create SQL Statement – the statement below is used to create the tbl_enrollment, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_enrollment` ( `enrollment_id` int(11) NOT NULL, `schedule_id` int(11) NOT NULL, `student_id` int(11) NOT NULL, `instructor_id` int(11) NOT NULL, `remarks` varchar(100) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_payment – this table of the project records the customer’s payment. This will also be the table where the income report will be retrieved. The said table has 8 columns.
- payment_id – this serves as the primary key of the table.
- payment_reference_number – reference number is system generated.
- student_id – This is a foreign key that links to the student table. It also represents the one who paid the transaction.
- enrollment_id – this columns links to the information of the enrollment table.
- amount – amount paid by the student.
- date_of_payment – the recorded payment date of the student.
- remarks – additional information about the payment record or transaction.
- user_id – processing officer
Create SQL Statement – the statement below is used to create the tbl_payment, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_payment` ( `payment_id` int(11) NOT NULL, `payment_reference_number` varchar(15) NOT NULL, `student_id` int(11) NOT NULL, `enrollment_id` int(11) NOT NULL, `amount` float NOT NULL, `date_of_payment` date NOT NULL, `remarks` varchar(100) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_progress_report – student can view their progress and teachers can rate the performance of their students using this table. The progress report table has 5 columns.
- training_name – refers to the driving lesson.
- student_id – foreign key that links to the student table.
- instructor_id – foreign key that connects to the instructor table. The instructor that have handled the driving lesson.
- score – the score of the student on a specific driving lesson. The instructor is the one who will rate and give scores to their students.
- remarks – message to the student on how to improve their driving skills.
Create SQL Statement – the statement below is used to create the tbl_progress_report, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_progress_report` ( `training_name` varchar(50) NOT NULL, `student_id` int(11) NOT NULL, `instructor_id` int(11) NOT NULL, `score` float NOT NULL, `remarks` varchar(100) 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` text 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.
- 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;
tbl_feature_access – The purpose of this table is to set permission or restrictions to the user as to what modules or features they can access. It has 8 columns
- access_id – primary key of the table
- user_id – foreign key that links to the user table.
- access_student – the value for this column is 0 or 1, 0 is disallowed and 1 is allowed to access.
- access_instructor – the value for this column is 0 or 1, 0 is not allowed and 1 is authorized to access.
- access_report – this column is 0 or 1, then 0 is forbidden and 1 is allowed.
- access_schedule – If the value is 0, then 0 is disallowed.
- access_enrollment – This column’s value is either 0 or 1, with 0 indicating that access is not permitted and 1 indicating that access is permitted.
- access_payment – This column’s value is either 0 or 1, with 0 indicating no access and 1 signifying yes.
Create SQL Statement – the statement below is used to create the tbl_feature_access, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_feature_access` ( `access_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `access_student` int(1) NOT NULL, `access_instructor` int(1) NOT NULL, `access_report` int(1) NOT NULL, `access_schedule` int(1) NOT NULL, `access_enrollment` int(1) NOT NULL, `access_payment` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Indexes for dumped tables
-- -- Indexes for table `tbl_enrollment` -- ALTER TABLE `tbl_enrollment` ADD PRIMARY KEY (`enrollment_id`), ADD KEY `schedule_id` (`schedule_id`), ADD KEY `student_id` (`student_id`), ADD KEY `instructor_id` (`instructor_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `tbl_feature_access` -- ALTER TABLE `tbl_feature_access` ADD PRIMARY KEY (`access_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `tbl_instructor` -- ALTER TABLE `tbl_instructor` ADD PRIMARY KEY (`instructor_id`); -- -- Indexes for table `tbl_payment` -- ALTER TABLE `tbl_payment` ADD PRIMARY KEY (`payment_id`), ADD KEY `student_id` (`student_id`), ADD KEY `enrollment_id` (`enrollment_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `tbl_progress_report` -- ALTER TABLE `tbl_progress_report` ADD KEY `student_id` (`student_id`), ADD KEY `instructor_id` (`instructor_id`); -- -- Indexes for table `tbl_schedule` -- ALTER TABLE `tbl_schedule` ADD PRIMARY KEY (`schedule_id`), ADD KEY `instructor_id` (`instructor_id`); -- -- Indexes for table `tbl_student` -- ALTER TABLE `tbl_student` ADD PRIMARY KEY (`student_id`); -- -- Indexes for table `tbl_user` -- ALTER TABLE `tbl_user` ADD PRIMARY KEY (`user_id`), ADD UNIQUE KEY `username` (`username`), ADD KEY `user_category_id` (`user_category_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_enrollment` -- ALTER TABLE `tbl_enrollment` MODIFY `enrollment_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_feature_access` -- ALTER TABLE `tbl_feature_access` MODIFY `access_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_instructor` -- ALTER TABLE `tbl_instructor` MODIFY `instructor_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_payment` -- ALTER TABLE `tbl_payment` MODIFY `payment_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_schedule` -- ALTER TABLE `tbl_schedule` MODIFY `schedule_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_student` -- ALTER TABLE `tbl_student` MODIFY `student_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;
Summary
Many driving schools are using Database systems to manage their operations. Such software includes accounting functions, student information management, customer service modules, and supply ordering. You must make an objective decision based on this knowledge to create a more informed decision on which type of software will best suit your needs. We hope that this article provided you with the details of different tables used for driving schools and how they can benefit your business.
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 links:
Blood Bank Information System Database Design
Lost and Found Information System Database Design
Daily Time Record System Database Design
Dairy Farm Management System Database Design
