Online Vehicle Ticketing System Database Design Sample

MySQL DBMS

Online Bus/Vehicle Ticketing System Database Design Sample – MySQL. With minor modifications or further generalization this database schema can be used to implement any kind of ticket booking system. eg. movie ticket booking system, events/show ticket booking etc.

Entities:

users – user details
user_type – type of users in system
vendors – providers’ information, in this case vehicle owner
vehicles – particular vehicle information
schedules – schedules of vehicles
routes – information of vehicle routes
vehicle_seats – vehicle seats information
tickets – actual ticket booking status etc stored here
media – images/videos etc related to particular vehicle stored here.
onlineticket

MySQL Create Script:

— phpMyAdmin SQL Dump
— version 3.2.0.1
http://www.phpmyadmin.net

— Host: localhost
— Generation Time: Apr 04, 2013 at 10:18 PM
— Server version: 5.1.37
— PHP Version: 5.3.0

SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;


— Database: `onlineticket`

— ——————————————————–


— Table structure for table `media`

CREATE TABLE IF NOT EXISTS `media` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`vehicle_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`path` varchar(255) NOT NULL,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=’table for storing images, video etc about vehicles’ AUTO_INCREMENT=1 ;


— Dumping data for table `media`

— ——————————————————–


— Table structure for table `routes`

CREATE TABLE IF NOT EXISTS `routes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`source` varchar(255) NOT NULL,
`destination` varchar(255) NOT NULL,
`distance` varchar(255) NOT NULL,
`travel_time` varchar(255) NOT NULL,
`stops` text NOT NULL,
`fare` varchar(255) NOT NULL,
`other_info` text,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


— Dumping data for table `routes`

— ——————————————————–


— Table structure for table `schedules`

CREATE TABLE IF NOT EXISTS `schedules` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`vehicle_id` int(11) NOT NULL,
`departure_time` varchar(255) NOT NULL,
`arrival_time` varchar(255) NOT NULL,
`status` int(11) NOT NULL COMMENT ‘schedule confirmed, delayed, cancelled’,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


— Dumping data for table `schedules`

— ——————————————————–


— Table structure for table `tickets`

CREATE TABLE IF NOT EXISTS `tickets` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ticket_no` varchar(255) NOT NULL,
`schedule_id` int(11) NOT NULL,
`seat_id` int(11) NOT NULL,
`vehicle_id` int(11) NOT NULL,
`route_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`status` int(11) NOT NULL,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


— Dumping data for table `tickets`

— ——————————————————–


— Table structure for table `users`

CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`type_id` int(11) NOT NULL,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


— Dumping data for table `users`

— ——————————————————–


— Table structure for table `user_type`

CREATE TABLE IF NOT EXISTS `user_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`slug` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


— Dumping data for table `user_type`

— ——————————————————–


— Table structure for table `vehicles`

CREATE TABLE IF NOT EXISTS `vehicles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`vendor_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`vehicle_info` text NOT NULL,
`number` varchar(255) NOT NULL,
`seats` int(11) NOT NULL,
`driver` varchar(255) NOT NULL,
`driver_phone` varchar(255) NOT NULL,
`route_id` int(11) NOT NULL,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


— Dumping data for table `vehicles`

— ——————————————————–


— Table structure for table `vehicle_seats`

CREATE TABLE IF NOT EXISTS `vehicle_seats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
`remark` varchar(255) NOT NULL,
`vehicle_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


— Dumping data for table `vehicle_seats`

— ——————————————————–


— Table structure for table `vendors`

CREATE TABLE IF NOT EXISTS `vendors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`logo` varchar(255) DEFAULT NULL,
`address` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`website` varchar(255) NOT NULL,
`user_id` int(11) NOT NULL,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


— Dumping data for table `vendors`

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Leave a Reply

Your email address will not be published. Required fields are marked *