Database Design of a Restaurant Management System from user story to relational Model
Tags: database, mysql, php
Published: 2020-11-30 20:51:51

The Restaurant Management System is a web application. This system is to automate day to day activity of a restaurant. Restaurant is a kind of business that serves people all over world with ready-made food. This system is to provide service facility to restaurant and also to the customer. This restaurant management system can be used by employees in a restaurant to handle the clients, their orders and can help them easily find free tables or place orders. The services that are provided is food ordering and reservation table management by the customer through the system, customer information management and waiter information management, menu information management and report. After a successful login the customer can access the menu page with the items listed according to the desired time. The main point of this system is to help restaurant administrator manage the restaurant business and help customer for online ordering and reserve table. The user can search for a menu according to his choice i.e. according to price range and category of food and later he can order a meal.

###USER STORY
**Restaurant Manager**
As a restaurant manager: I should be able to update information about my restaurant
As a restaurant manager: I should be able to view weekly sales for my outlet
As a restaurant manager: I should be able to view the current inventory position of the restaurant
**Customer**
As a customer: I should be able to browse through the menu and look at the various food options available in the restaurant along with the price for each item.
As a customer: I should be able to select items from the menu and add them to my cart/order
As a customer: I should have a cart containing all the chosen items so far, accessible all the time
**Chef**
As a chef: I should be able to view the current queue of orders
As a chef: I should be able to accept the orders
As a chef: I should be able to update the order status to ready once the order is prepared
**Waiter**
As a waiter: I should be able to view the current queue to see the ready orders for pickup
As a waiter: I should be able to mark the orders as picked up on the common queue
As a waiter: I should be able to view the feedback of customers concerning the meal and service
**Supplier**
As a supplier: I should be able to view the inventory items so as to supply the lacking ingredients to the chef
As a supplier: I should be able to update the food items as available when supplied
As a supplier: I should be able to update information about the ingredients/food stuff and some of their advantages when consumed

###ER-DIAGRAM

![draw-2-IO (1)](https://dev-to-uploads.s3.amazonaws.com/i/7b2bvv3e02pal4hga8qz.png)

An E-R diagram is used to represent the E-R model. It contains all known entities for the given scenario, their attributes, identifiers, and the relationships that exist among the entities.

As seen on figure above the ER diagram has six entities which are made up of various attributes including the entity’s primary key and other attributes like the name, salary, address and phone.

###RELATIONAL MODEL
![restaurant1](https://dev-to-uploads.s3.amazonaws.com/i/kt28n435hggi27373dpm.png)
 
The relational diagram is a graphical representation of relational databases' entities, attributes that are present in those entities and the relationship among these entities.

#####CHEF
Chef id	is the unique id to identify the chef with a datatype of integer
Chef name is Identity of the chef with a datatype of varchar
Salary is Remuneration of the chef at the end of the month with a datatype of float

#####MEAL
Meal id	is the unique identifier of the meal table with a datatype of integer
Price is the amount tagged to a particular quantity of food with a datatype of float
Name is the identifier of the meal with a datatype of varchar
Chef id	Unique identifier comes to meal because of the many to one relationship

#####SUPPLIER
Supplier id is The unique id to identify the supplier with a datatype of integer
Supplier name is The name to identify the supplier with a datatype of varchar
Supplier city is The town in which the supplier lives in with a datatype of varchar
Phone is The digits used to get in contact with the supplier with a datatype of integer
Chef id	Unique identifier comes to supplier table because of the many to one relationship

#####CUSTOMER
Customer id is The unique id to identify the customer with a datatype of integer
Customer name is The name to identify the customer with a datatype of varchar
Address is The particular place where the customer lives with a datatype of  varchar
Phone is The digits used to get in contact with the customer with a datatype of integer
Waiter id is Unique identifier comes to customer because of the many to one relationship

#####WAITER
Waiter id is The unique id to identify the waiter with a datatype of integer
Name is The name to identify the waiter with a datatype of varchar
Salary	Remuneration of the waiter at the end of the month with a datatype of float
Phone is The digits used to get in contact with the waiter with a datatype of integer

#####INGREDIENT
Ingredient id is The unique id to identify the waiter with a datatype of integer
Name is The name to identify the ingredient with a datatype of varchar
Description is What the meal is all about with a datatype of varchar
Meal id	is the Unique identifier comes to ingredient table because of the many to one relationship

#####ORDERS
Customer id is The unique id to identify the customer with a datatype of integer
Meal id	The unique identifier of the meal table with a datatype of integer
 
#####PROVIDES
Supplier id is The unique id to identify the supplier with a datatype of integer
Ingredient id is The unique id to identify the waiter with a datatype of integer

###NORMALIZATION

The table is already in the first normal form because all attributes are atomic and each table is identified by its own unique attribute.
The table is also in the second normal form because all non-prime attributes are functionally dependent on the prime key attribute.
But the table is not in the third normal form because we find out in the supplier table that the supplier address can be dependent on the supplier city so we split the supplier table and take out the address attribute so that there should be no functional or partial dependencies.

![normalization](https://dev-to-uploads.s3.amazonaws.com/i/gocq0m4w7qibxttt4irm.png)
 

In conclusion the restaurant management system helps to facilitate the work of a restaurant manager to manage the restaurant business and help customer for online ordering and reserve table. Thank you and hope you enjoyed.