Online Food Ordering System Database Design

Online Food Ordering System Database Design

Online food ordering system is a web based platform designed to cater the orders of customer through this platform.

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

tblmenu – this table will store the list of menu/food. It has 3 columns as presented below.

• menu_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).
• menu_name – the name of the menu.
• price – the retail amount of the food.
• menu_type_id – this is a foreign key that links to the tblmenutype, this column refers to the type of menu or the category the food belongs to.
• menu_image – the picture of the menu or food.
• ingredients – the specific list of ingredients used in the menu/food.
• menu_status – this refers to the availability of the menu; 0 means not available, 1 means available.

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

CREATE TABLE IF NOT EXISTS `tblmenu` (
`menu_id` int(11) NOT NULL AUTO_INCREMENT,
`menu_name` varchar(100) NOT NULL,
`price` float NOT NULL,
`menu_type_id` int(11) NOT NULL,
`menu_image` blob NOT NULL,
`ingredients` varchar(500) NOT NULL,
`menu_status` int(1) NOT NULL,
PRIMARY KEY (`menu_id`),
KEY `menu_type_id` (`menu_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblmenutype – this table holds the records of the different food category. The table has 3 columns.

• menu_type_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).
• type_name – the category name of the menu.
• description – description or brief explanation about the menu type.

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

CREATE TABLE IF NOT EXISTS `tblmenutype` (
`menu_type_id` int(11) NOT NULL AUTO_INCREMENT,
`type_name` varchar(50) NOT NULL,
`description` varchar(100) NOT NULL,
PRIMARY KEY (`menu_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcustomer – information of the customer will be stored in this table and it 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).
• customer_first_name – first name of the customer.
• customer_last_name – last name of the customer.
• customer_middle_name – middle name of the customer.
• customer_email – email address of the customer. Notification and confirmation of orders will be sent in the email of the customer.
• customer_phone_number – cellphone number of the customer. Notification and confirmation of orders will be sent in the email of the customer
• customer_landline – landline contact of the customer
• profile_image – profile image of the customer.
• customer_username – desired username of the customer.
• customer_password – desired password of the customer.
• account_status – account status refers to if the customer is still active or not.

Create SQL Statement – the statement below is used to create the tblcustomer, 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_first_name` varchar(30) NOT NULL,
`customer_last_name` varchar(30) NOT NULL,
`customer_middle_name` varchar(30) NOT NULL,
`customer_email` varchar(50) NOT NULL,
`customer_phone_number` varchar(15) NOT NULL,
`customer_landline` varchar(15) NOT NULL,
`profile_image` blob NOT NULL,
`customer_username` varchar(30) NOT NULL,
`customer_password` varchar(30) NOT NULL,
`account_status` int(1) NOT NULL,
PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser – information of authorized user of the system are stored in this table and it has 6 columns.

• 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).
• full_name – complete name of the user.
• contact – contact information (phone or landline).
• email_address – email address of the user.
• username – username of the user.
• password – password of the user.

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(100) NOT NULL,
`contact` varchar(50) NOT NULL,
`email_address` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblorder – customer orders are stored in the tblorder and this table has 6 columns.

• order_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).
• customer_id – this is the foreign key that links to the customer table. It refers to the customer who ordered the food.
• order_date – the date of order.
• total_amount – the amount to be paid by the customer
• order_status – status of order (0-pending, 1-confirmed, 2-cancelled).
• processed_by – this is a foreign key that connects or links to the user table. It refers to the user who processed the transaction.

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

CREATE TABLE IF NOT EXISTS `tblorder` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`order_date` date NOT NULL,
`total_amount` float NOT NULL,
`order_status` int(1) NOT NULL,
`processed_by` int(11) NOT NULL,
PRIMARY KEY (`order_id`),
KEY `customer_id` (`customer_id`,`processed_by`),
KEY `processed_by` (`processed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblorderdetails – order details stores the detailed customer orders of the customer. The table has 6 columns.

• order_details_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).
• order_id – foreign key that links to the order table.
• menu_id – foreign key that links to the menu table.
• amount – amount of the food or menu.
• no_of_serving – refers to the number of orders.
• total_amount – total amount is equal to amount multiply by the number of serving.

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

CREATE TABLE IF NOT EXISTS `tblorderdetails` (
`order_details_id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`menu_id` int(11) NOT NULL,
`amount` float NOT NULL,
`no_of_serving` int(4) NOT NULL,
`total_amount` float NOT NULL,
PRIMARY KEY (`order_details_id`),
KEY `order_id` (`order_id`,`menu_id`),
KEY `menu_id` (`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpayment – payment transactions are recorded and stored on this table. This table has 6 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).
• order_id – foreign key that points out to the order table.
• amount – amount paid by the customer.
• paid_by – name of person who paid the transactions.
• payment_date – date of payment.
• processed_by – this is a foreign key that connects or links to the user table. It refers to the user who processed the transaction.

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,
`order_id` int(11) NOT NULL,
`amount` float NOT NULL,
`paid_by` varchar(50) NOT NULL,
`payment_date` date NOT NULL,
`processed_by` int(11) NOT NULL,
PRIMARY KEY (`payment_id`),
KEY `order_id` (`order_id`,`processed_by`),
KEY `processed_by` (`processed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblrating – rating and comments of the customers are stored in this table. It has 5 columns.

• rating_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).
• menu_id – foreign key that links to the menu table.
• score – score provided by the customer (1-5 scale, 1 as the lowest and 5 as the highest)
• remarks – comments, suggestions and recommendations of the customers.
• date_recorded
• customer_id – foreign key that links to customer information.

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

CREATE TABLE IF NOT EXISTS `tblrating` (
`rating_id` int(11) NOT NULL AUTO_INCREMENT,
`menu_id` int(11) NOT NULL,
`score` int(1) NOT NULL,
`remarks` varchar(100) NOT NULL,
`date_recorded` date NOT NULL,
`customer_id` int(11) NOT NULL,
PRIMARY KEY (`rating_id`),
KEY `menu_id` (`menu_id`),
KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblsiteinfo – information of the website or company is stored in this table.

• site_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).
• site_name – name of the website.
• description – description of the company.
• contact_info – contact information of the company.
• address – address of the company.
• last_update – the date the information was last updated.

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

CREATE TABLE IF NOT EXISTS `tblsiteinfo` (
`site_info_id` int(11) NOT NULL AUTO_INCREMENT,
`site_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`contact_info` varchar(15) NOT NULL,
`address` varchar(100) NOT NULL,
`last_update` date NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`site_info_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints for dumped tables
--
-- Constraints for table `tblmenu`
--
ALTER TABLE `tblmenu`
ADD CONSTRAINT `tblmenu_ibfk_1` FOREIGN KEY (`menu_type_id`) REFERENCES `tblmenutype` (`menu_type_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblorder`
--
ALTER TABLE `tblorder`
ADD CONSTRAINT `tblorder_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblorder_ibfk_1` FOREIGN KEY (`processed_by`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblorderdetails`
--
ALTER TABLE `tblorderdetails`
ADD CONSTRAINT `tblorderdetails_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `tblorder` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblorderdetails_ibfk_1` FOREIGN KEY (`menu_id`) REFERENCES `tblmenu` (`menu_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblpayment`
--
ALTER TABLE `tblpayment`
ADD CONSTRAINT `tblpayment_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `tblorder` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblpayment_ibfk_1` FOREIGN KEY (`processed_by`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblrating`
--
ALTER TABLE `tblrating`
ADD CONSTRAINT `tblrating_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `tblcustomer` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblrating_ibfk_1` FOREIGN KEY (`menu_id`) REFERENCES `tblmenu` (`menu_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblsiteinfo`
--
ALTER TABLE `tblsiteinfo`
ADD CONSTRAINT `tblsiteinfo_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.