Bus Booking System Database Design

Bus Booking System Database Design

The project entitled bus booking system is an online platform intended for the travelers and commuters of bus and to the management of the bus company.

This article will provide you with the list of tables and entities for every table in the development of bus booking system. The team will later provide a video tutorial on how to create the database in PHPMyAdmin.

tblbus – this is the table that will hold the information of the bus and following are the list of attributes or field columns.

bus_id – this is the primary key of the table and serves as the unique value of the table and usually set to auto_increment.
bus_number – the bus number is the number assigned by the company for monitoring purposes.
bus_plate_number – it refers to the vehicle registration number of the bus.
bus_type – bus type field has two values; the aircon and non-aircon units.
capacity – this field represents the total number of persons the bus can accommodate including the driver.
user_id – this is the foreign key of the table that points out to the user who encoded the bus information.

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

CREATE TABLE IF NOT EXISTS `tblbus` (
`bus_id` int(11) NOT NULL AUTO_INCREMENT,
`bus_number` varchar(15) NOT NULL,
`bus_plate_number` varchar(15) NOT NULL,
`bus_type` int(1) NOT NULL,
`capacity` int(3) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`bus_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbldriver – this is the table that will store the records of the driver information and the table has 3 fields and column.

driver_id – this is the primary key of the table and serves as the unique value of the table and usually set to auto_increment.
driver_name – the full name of the driver.
driver_contact – contact information of the driver preferably the cellphone number.
user_id – this is the foreign key of the table that points out to the user who encoded the driver information.

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

CREATE TABLE IF NOT EXISTS `tbldriver` (
`driver_id` int(11) NOT NULL AUTO_INCREMENT,
`driver_name` varchar(50) NOT NULL,
`driver_contact` varchar(15) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`driver_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcustomer – customer information are stored in this table and it has the following entities:

customer_id – this is the primary key of the table and serves as the unique value of the table and usually set to auto_increment.
customer_name – the full name of the customer.
customer_contact – this is the column that stores the cellphone number of the customer for the sms notification of the booking.
customer_email – stores the email address of the customers.
username – the desired username of the customer.
password – the desired password of the customer.
account_status – account status refers to whether the account is active or not.
user_id – this is the foreign key of the table that points out to the user who encoded the customer information.

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

CREATE TABLE IF NOT EXISTS `tblcustomer` (
`customer_id` int(11) NOT NULL AUTO_INCREMENT,
`customer_name` varchar(50) NOT NULL,
`customer_contact` varchar(15) NOT NULL,
`customer_email` varchar(30) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`account_status` int(1) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`customer_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbltravelschedule – the administrator will encode the travel schedule every day and it will be stored and archived in this table. The travel schedule table has the following attributes:

schedule_id – this is the primary key of the table and serves as the unique value of the table and usually set to auto_increment.
bus_id – foreign key that points out to the bus information.
driver_id – foreign key that points out to the driver information.
starting_point – this column represents the point of origin of the bus.
destination – is the point where a bus route
schedule_date – refers to the date of travel.
departure_time – refers to the time of departure.
estimated_arrival_time – the time the bus will arrive in its destination, estimate only.
fare_amount – the fare amount to be paid by the commuter.
remarks – important notes on the travel.
user_id – this is the foreign key of the table that points out to the user who encoded the travel schedule information.

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

 CREATE TABLE IF NOT EXISTS `tbltravelschedule` (
`schedule_id` int(11) NOT NULL AUTO_INCREMENT,
`bus_id` int(11) NOT NULL,
`driver_id` int(11) NOT NULL,
`starting_point` varchar(30) NOT NULL,
`destination` varchar(30) NOT NULL,
`schedule_date` date NOT NULL,
`departure_time` time NOT NULL,
`estimated_arrival_time` time NOT NULL,
`fare_amount` float NOT NULL,
`remarks` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`schedule_id`),
KEY `bus_id` (`bus_id`,`driver_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `driver_id` (`driver_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblbooking – booking table is the table that holds and stores the booking information of the customer. The booking table has 9 attributes and they are the following:

booking_id – this is the primary key of the table and serves as the unique value of the table and usually set to auto_increment.
schedule_id – this is the foreign key that points out to the schedule selected by the customer.
customer_id – this is the foreign key that points out to the customer information.
number_of_seats – the number of seats the customer wants to reserve.
fare_amount – the amount per seat.
total_amount – total amount to be paid.
date_of_booking – date of booking.
booking_status – booking status refers to whether the booking was approved or cancelled.
user_id – this is the foreign key of the table that points out to the user who processed the booking information.

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

CREATE TABLE IF NOT EXISTS `tblbooking` (
`booking_id` int(11) NOT NULL AUTO_INCREMENT,
`schedule_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`number_of_seats` int(2) NOT NULL,
`fare_amount` float NOT NULL,
`total_amount` float NOT NULL,
`date_of_booking` datetime NOT NULL,
`booking_status` int(1) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`booking_id`),
KEY `schedule_id` (`schedule_id`,`customer_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpayment – the payment table is the table that records the payment transaction of the customers.

payment_id – this is the primary key of the table and serves as the unique value of the table and usually set to auto_increment.
booking_id – it is the foreign key that points out to the booking information.
amount_paid – the amount paid by the customer.
payment_date – refers to the date of payment.
user_id – this is the foreign key that points out to the user who processed the payment.

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

CREATE TABLE IF NOT EXISTS `tblpayment` (
`payment_id` int(11) NOT NULL AUTO_INCREMENT,
`booking_id` int(11) NOT NULL,
`amount_paid` float NOT NULL,
`payment_date` date NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`payment_id`),
KEY `booking_id` (`booking_id`,`user_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser – this is the table that stores information of the users who can access the system.

user_id – this is the primary key of the table and serves as the unique value of the table and usually set to auto_increment.
full_name – full name of the employee.
contact_no – contact information of the employee.
email_address – email address of the employee.
username – the assigned username of the employee
userpassword – the assigned password of the employee, they are allowed to change their password.
account_category – account category refers to their job role, such as the manager, encoder, cashier, etc.
account_status – account status refers to whether the account is active or not.

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

CREATE TABLE IF NOT EXISTS `tbluser` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`full_name` varchar(50) NOT NULL,
`contact_no` varchar(15) NOT NULL,
`email_address` varchar(30) NOT NULL,
`username` varchar(30) NOT NULL,
`userpassword` varchar(30) NOT NULL,
`account_category` int(1) NOT NULL,
`account_status` int(1) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Bus Booking System Database Design - List of Tables — Bus Booking System Database Design – List of Tables

Constraints for dumped tables
—
— Constraints for table `tblbooking`
—
ALTER TABLE `tblbooking`
ADD CONSTRAINT `tblbooking_ibfk_3` FOREIGN KEY (`schedule_id`) REFERENCES `tbltravelschedule` (`schedule_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblbooking_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblbooking_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

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

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

—
— Constraints for table `tblpayment`
—
ALTER TABLE `tblpayment`
ADD CONSTRAINT `tblpayment_ibfk_2` FOREIGN KEY (`booking_id`) REFERENCES `tblbooking` (`booking_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpayment_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbltravelschedule`
—
ALTER TABLE `tbltravelschedule`
ADD CONSTRAINT `tbltravelschedule_ibfk_3` FOREIGN KEY (`bus_id`) REFERENCES `tblbus` (`bus_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbltravelschedule_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbltravelschedule_ibfk_2` FOREIGN KEY (`driver_id`) REFERENCES `tbldriver` (`driver_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Constraints for dumped tables
—

—
— Constraints for table `tblbooking`
—
ALTER TABLE `tblbooking`
ADD CONSTRAINT `tblbooking_ibfk_3` FOREIGN KEY (`schedule_id`) REFERENCES `tbltravelschedule` (`schedule_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblbooking_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblbooking_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

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

Free Download .sql file

Our team can modify the project based on your specific business requirements.

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

Hire our team to do the project.

Post navigation

Similar Articles

Visitors Log Monitoring System for BJMP Capstone Project

Online Catering Reservation Capstone Project Proposal

File Management System in PHP and MySQL