Online Catering System Database Project

Online Catering System Database Project

About the Project

Nowadays, major marketing strategies aimed through INTERNET. Most business enterprises seek to offer their services through online. Thus, the researcher of the system entitled Online Catering Reservation proposed a system that offers services that is style, portable, accessible and easy to use. Since, internet are available everywhere it easy for the users to book a reservation even they’re far from the location. This system gives much advantage for those busy person because they only have to check then book a reservation on their cellular phone with internet without hassle. This system will be a friendly user so that everyone will be applicable to use it. With the help with this system the user can now automatically book reservations as well as they have the ability to check the availability quickly.

Database Tables

Let’s start by making the tables and columns. For a thorough tutorial, please watch the video.

tbl_customer – this table will store the information of the customer registered in the system. Based on the database design, the customer can login using their username and password. Prior to that, they need to submit a proof of identification such as valid ID or any valid documents required. The customer table of the project has 11 columns.

• 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).
• lastname – the last name of the customer
• firstname – the first name of the customer
• middlename – the middle name of the customer
• email_address – the email address of the customer
• contact_number -the contact number of the customer preferably mobile number
• complete_address – the complete address of the customer
• valid_id – valid id of the customer
• username – the preferred username of the customer combined with the password to login to the system
• password – the preferred password of the user used to login to the system
• account_status – active or inactive

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,
`lastname` varchar(30) NOT NULL,
`firstname` varchar(30) NOT NULL,
`middlename` varchar(30) NOT NULL,
`email_address` varchar(50) NOT NULL,
`contact_number` varchar(15) NOT NULL,
`complete_address` text NOT NULL,
`valid_id` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_food_category – this table will store the details of the food categories in the system. Food items are grouped and categorized. This table has only two columns.

• 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 name of the food category

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

CREATE TABLE `tbl_food_category` (
`category_id` int(11) NOT NULL,
`category_name` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_food – this table stores the information of the foods in the online catering system. Food table of the project has six fields.

• food_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_id
• food_name – the name of the food
• description – additional information about the food
• image – this is used to upload the image of the food
• amount – the amount to be paid for the food
• availability – available, not available

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

CREATE TABLE `tbl_food` (
`food_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`food_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`image` varchar(100) NOT NULL,
`amount` float NOT NULL,
`availability` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_package – this table stores the information of the food packages offered by the catering services. Food package table allows the catering business owners to combine different foods that will cater to the different types of events. Package table has six columns.

• package_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).
• package_name – the name of the package
• package_details – the details of the package
• amount – the amount to be paid for the package.
• image – this is use to upload image of the food packages
• availability – available, not available

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

CREATE TABLE `tbl_package` (
`package_id` int(11) NOT NULL,
`package_name` varchar(30) NOT NULL,
`package_details` varchar(150) NOT NULL,
`amount` float NOT NULL,
`image` varchar(100) NOT NULL,
`availability` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_event_type – this table stores the information of the event type to be catered by the online catering services and it has three database fields.

• event_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).
• event_name -the name of the event
• description – additional information about the event type

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

CREATE TABLE `tbl_event_type` (
`event_id` int(11) NOT NULL,
`event_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_reservation – this table store the information of the reservations made by the customer using the online catering system.  Reservation table contains eight columns.

• reservation_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).
• reference_no – reference number of the reservation
• event_type_id – this is a foreign key that points out to the event type
• customer_id – this is a foreign key that points out to the customer
• date- the date set for the reservation
• status – pending, approved, cancelled, rescheduled
• remarks – additional information about the reservation
• processed_by (user_id) – this is a foreign key that points out to the user who processed the reservation

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

CREATE TABLE `tbl_reservation` (
`reservation_id` int(11) NOT NULL,
`reference_no` varchar(15) NOT NULL,
`event_type_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`date` date NOT NULL,
`status` int(1) NOT NULL,
`remarks` varchar(50) NOT NULL,
`processed_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_reservation_details – this table stores the reservation details made using the system. Details of the reservation such as the food items ordered or packages selected by the customers are stored in this table. The said table has seven columns.

• detail_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).
• reservation_id -this is a foreign key that points out to the reservation
• food_id – this is a foreign key that points out to the food
• amount – the amount to be paid for the food
• package_id – this is a foreign key that points out to the package
• amount – the amount to be paid for the food package
• total_amount – total amount to be paid for the reservation

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

CREATE TABLE `tbl_reservation_details` (
`detail_id` int(11) NOT NULL,
`reservation_id` int(11) NOT NULL,
`food_id` int(11) NOT NULL,
`amount` float NOT NULL,
`package_id` int(11) NOT NULL,
`package_amount` float NOT NULL,
`total_amount` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_payment – this table stores the information of the payment made by the customers using the online catering system.  Payment table of the project has seven columns.

• payment_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).
• reservation_id – this is a foreign key that points out to the reservation
• reference_number – reference number of the payment
• paid_by – the name of the payor
• date – the date the payment was made
• amount_paid – the amount paid by the customer
• processed_by (user_id) – this is a foreign key that points out to the user who processed the payment

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,
`reservation_id` int(11) NOT NULL,
`reference_number` varchar(15) NOT NULL,
`paid_by` varchar(100) NOT NULL,
`date` date NOT NULL,
`amount_paid` float NOT NULL,
`processed_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_company_info – this table stores the information of the company registered in the online catering system and it has seven database columns.

• info_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).
• company_name – the name of the company
• complete_address – complete address of the company
• email_address – the email address of the company
• website – the name of the company’s website
• facebook_page – the facebook page of the company
• business_permit – the business permit of the company

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

CREATE TABLE `tbl_company_info` (
`info_id` int(11) NOT NULL,
`company_name` varchar(100) NOT NULL,
`complete_address` text NOT NULL,
`email_address` varchar(50) NOT NULL,
`website` varchar(100) NOT NULL,
`facebook_page` varchar(100) NOT NULL,
`business_permit` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user – this table stores the information of the users of the online catering 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 – the preferred username of the user to login to the system
• password – the preferred password of the user used to login to the system
• complete_name – the complete name of the user
• valid_id – valid id of the user
• email_address – the email address of the user
• contact_number – the contact number of the user preferably mobile number
• user_category – admin, staff
• account_status – active or inactive

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,
`complete_name` varchar(100) NOT NULL,
`valid_id` varchar(100) NOT NULL,
`email_address` varchar(50) NOT NULL,
`contact_number` varchar(15) NOT NULL,
`user_category` int(1) NOT NULL,
`account_status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

FREE DOWNLOAD DATABASE

Summary

The Online Catering System Database is a web-based application that helps users plan and manage catering events. The system provides a convenient way for users to search for catering services, compare prices, and book appointments. The database also offers a variety of tools to help users manage their events, including a guest list manager, a budget planner, and a menu planner.

The Online Catering System Database is a valuable tool for party planners. It provides a convenient way to find catering services, compare prices, and book appointments. The system also offers a variety of tools to help users manage their events, including a guest list manager, a budget planner, and a menu planner. The online database is also a useful resource for users who are looking for ideas for their next catering event.

We hope that this article has provided you the idea on how to design and prepare the database structure for the project on online catering system.

Readers are also interested in:

Restaurant Food Delivery System Free Database Design Tutorial

Online Food Catering Services Management System with Payment Gateway

85 Best Management System Project Ideas

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.