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.
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 */;
0 comments :
Post a Comment