Database Design Practice - Restaurant

---

Overview

For this project, I was tasked with choosing a small business model that may benefit from the use of a database, and to design and create a working prototype of said database. The domain chosen for this project was the modeling of a restaurant. This includes the interactions between employees, customers, orders, order details, and reservations. The goal of this project is to accurately model the entities and relations of a generic restaurant, then create a database with relevant information to the model. This process began with defining business rules, determining the necessary information to be stored, and designating the subsequent entities.

Business Rules

• A CUSTOMER can have zero to many ORDERS
• A CUSTOMER can have zero to many RESERVATIONS
• A CUSTOMER is limited to one RESERVATION a day
• An EMPLOYEE can be attached to zero to many ORDERS
• An EMPLOYEE can only be MANAGED by one person
• AN EMPLOYEE can only belong to one permanent ADDRESS
• An ORDER is associated with one and only one CUSTOMER
• An Order can have one to many MENU_ITEMS
• A MENU_ITEM can exist in zero to many ORDERS
• A MENU_ITEM must be of the item type FOOD or DRINK
• A CUSTOMER can only order an item if there is INVENTORY
• A CUSTOMER can only order as many of an item as there is INVENTORY
• A RESERVATION cannot be canceled
• There are no returns or refunds

Entities

• Employees
  • emp_id
  • fname
  • lname
  • title
  • street
  • city
  • zip_code
  • emp_phone
  • gender
  • salary
  • start_date
  • end_date (NULL if still employed)
• City
  • zip_code
  • city
  • state
• Customers
  • customer_id
  • customer_fname
  • customer_lname
  • customer_phone
• Orders
  • order_id
  • emp_id
  • customer_id
  • order_date
• Order_Details
  • order_details_id
  • order_id
  • item_id
  • quantity
• Reservations
  • reservations_id
  • customer_id
  • reservations_date
• Menu_Item
  • item_id
  • item_name
  • item_price
  • inventory
  • item_type
• Food_Item
  • item_id
  • calories
  • gluten_free
  • vegan
• Drink_Item
  • item_id
  • alcoholic

Definitions

• An Employee is someone who is on payroll for the restaurant. These are individuals who are currently working, or have previously worked, for the restaurant
• A Customer is an individual who interacts with the restaurant in the form of placing an order.
• Zip is a table containing address information for Employees per their associated postal codes. It keeps track of the city and state associated with each postal code
• Orders are taken by Employees and placed by Customers. They contain unique Order IDs which are used to find the corresponding details of the order placed
• Order Details are the items ordered and quantity ordered by the Customer
• Reservations are made by Customers wishing to dine-in on a specific date at a specific time
• Menu Items are the items available for Order by Customers at the restaurant. This entity keeps track of price, inventory, and item_type. The inventory variable keeps track of how many items the restaurant has in stock
• Food Items are a subtype of menu items and keep track of the food items and associated food information
• Drink Items are a subtype of menu items and keep track of the drink items and whether they are alcoholic or not

As discussed earlier, the first layers of the model involve image processing steps (normalization & augmentation). This is followed by 3 convolutional, each followed by a pooling and dropout layer. Lastly, the input is flattened and passed through a final dense layer. The optimizer chosen to train the model was Adam. The model was trained for 10 epochs, with a batch size of 32. An early stopping callback was implemented to mitigate the chance of overfitting. The comparison of loss and accuracy between the training and validation datasets can be seen below. I typically would have tried training it for more epochs but my RAM was limited and I didn't want to compromise image resolution.

As you can see, there is minimal overfitting, training and validation accuracy & loss are closely aligned. An indicator of overfitting would be if validation accuracy or loss stalls out while training accuracy continues to improve. When this happens it's good practice to reduce the number of epochs, add dropout layers to the model, or try to make the training set more diverse. In fact, we should probably run this model for more epochs to test how far we could go before overfitting (and we could probably increase performance) but Google Colab is limiting my RAM so more epochs will probably crash the runtime.

EER Diagram

Below is the final iteration of the Enhanced Entity Relationship Diagram (EERD). This provides a descriptive reference of the database design and entity-relationships that will be integrated into the database.

Next, we converted our EER diagram into a normalized database design. Primary keys for each table are highlighted in blue and have a solid underline. Foreign keys are depicted with a dotted underline. For instance, the primary key for the ORDER table is "order_id" and the foreign keys are "emp_id" and "customer_id".

Overall, this project served as a great introduction to database design. I learned a lot about the design process particularly how important it is to ensure it's organized and flexible while also making sure the design accounts for the potential growth of the company. I also gained experience with DDL and DML in MySQL to design and manipulate database architecture. The code can be found at the github link below.