Queuing System Free Database Design Tutorial
Introduction
A queuing system is a system that assists in the management of queues. It separates the line into sub-lines, or queues, with one section of the line being referred to as the “head” of the queue and another section of the line being referred to as the “back” of the queue.
Database Design Tutorial
Developers are required to develop a queuing system to manage to wait for customers in an orderly fashion. The biggest challenge with creating a queuing system is that it’s not easy to predict how many customers will arrive at any given time, therefore causing the developer the difficulty of assessing the limitations of the number of servers available.
Therefore, this article will provide you with the optimize database design to go through all of these possibilities and easy methods, which will enable developers to develop robust, efficient, and intelligent queues which would best suit their business needs.
tbl_customer – this table will store the information of the customers in the system.
- customer_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 – the complete name of the customer
- contact_number – the contact number of the customer, preferrably mobile or cellphone number.
Create SQL Statement – the statement below is used to create the tbl_customer, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_customer` ( `customer_id` int(11) NOT NULL, `complete_name` varchar(50) NOT NULL, `contact_number` varchar(15) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_counter – the counter’s information will be stored in this table.
- counter_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).
- counter_number – the counter’s number
Create SQL Statement – the statement below is used to create the tbl_counter, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_counter` ( `counter_id` int(11) NOT NULL, `counter_number` int(2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_category – this table will store the details of the que categories in the system.
- category_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).
- category_name – the category’s name
- description – description or additional information about the category
Create SQL Statement – the statement below is used to create the tbl_category, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_category` ( `category_id` int(11) NOT NULL, `category_name` varchar(30) NOT NULL, `description` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_que_list – this table will store the information of the que list in the system.
- que_list_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).
- counter_id – this is a foreign key that points out to the counter
- customer_id – this is a foreign key that points out to the customer
- category_id- this is a foreign key that points out to the category.
- date_time – the date and time the que was recorded
- status – pending, completed
Create SQL Statement – the statement below is used to create the tbl_que_list, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_que_list` ( `que_list_id` int(11) NOT NULL, `counter_id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `date_time` datetime NOT NULL, `status` int(1) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_ads_management – this table will store the information of the ads while in que in the system.
- ads_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).
- video_image_upload – this will hold the videos or images of ads uploaded
- status -pending, played
Create SQL Statement – the statement below is used to create the tbl_ads_management, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_ads_management` ( `ads_id` int(11) NOT NULL, `video_image_upload` text NOT NULL, `status` int(1) 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` longblob NOT NULL, `fullname` varchar(50) NOT NULL, `contact` varchar(15) NOT NULL, `email` varchar(30) 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 (this field will group the user based on their designation).
- 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(15) NOT NULL, `description` varchar(50) 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;
Summary
Having completed this tutorial, we now understand the different tables that are utilized in the development of an online donation platform. Our online donation platform system tables were created using phpMyAdmin; however, you are free to change, modify, and upgrade the tables to meet user requirements.
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.
Teller’s Queuing System User’s Manual
Person with Disability Information System Database Design
Vehicle Parking Management System Database Design
