🍽️ Restaurant Booking & Order Management System

Database Redesign Project
Author: Oscar Simeon
Date: 2024-10-16

---

📘 Overview

This project focuses on the redesign of a relational database schema for a restaurant’s core operations — including table bookings, food orders, menu and staff management, and customer payments.

The redesigned schema introduces normalized structures, enforces data integrity using constraints, and supports real-time tracking and reporting.

---

🎯 Objectives

• Improve readability and naming consistency across the schema
  
• Normalize data to remove redundancy and improve data efficiency
  
• Enforce constraints such as UNIQUE, DEFAULT, and ENUM
  
• Add support for status tracking, timestamps, and payment logging

---

🛠️ Key Schema Improvements

Feature	Old Schema	New Schema	Purpose
Table Naming	Tbl_order, menu_item	TABLE ORDER, MENUITEM	Standardized & descriptive
Shift Field	Free text shift	ENUM('MORNING', 'EVENING', 'FULL')	➤ Prevents invalid input
Order/Menu Link	ORDERMENUITEM(order_id)	Composite PK: ORDER ID, MENUITEM ID	Enables multiple items per order
Price Field	FLOAT	DECIMAL(8,2)	➤ Improves currency precision
Status Tracking	Not available	Fields like ORDER STATUS, RESERVATION STATUS, PAYMENT STATUS	Enables real-time system state
Timestamps	Missing	CREATED_AT, UPDATED_AT	➤ Supports audit trails and analysis

---

🔄 Core Entity Relationships

• TABLE: Represents physical tables. Linked to TABLE ORDER and RESERVATION.
  
• WAITER: Staff member handling orders; has SHIFT and CONTACT NO.
  
• TABLE ORDER: Stores orders placed at a table, linked to a WAITER.
  
• CUSTOMER: Holds customer data with unique NIC NO.
  
• MENU: Categorizes menu offerings (e.g., Breakfast, Lunch).
  
• MENUITEM: Individual items (e.g., “Pancakes”, “Rice”). Linked to a MENU.
  
• ORDER MENUITEM: A junction table linking orders with items and QUANTITY.
  
• RESERVATION: Holds reservation details with links to TABLE, CUSTOMER, and optionally ORDER.
  
• PAYMENT: Stores payment method, amount, status, and timestamps for orders.

---

💡 Design Highlights & Take Notes

• ✅ Use of ENUM fields (e.g., SHIFT, ORDER STATUS, PAYMENT METHOD) reduces errors by enforcing valid input values.
  
• ✅ Composite Primary Key on ORDER MENUITEM enables flexible, multi-item orders.
  
• ✅ DECIMAL(8,2) used instead of FLOAT for accurate currency storage.
  
• ✅ Status fields (RESERVATION STATUS, PAYMENT STATUS) support live tracking and better analytics.
  
• ✅ Timestamps (CREATED_AT, UPDATED_AT) allow historical tracking and audit trails.

---

💾 Sample SQL Snippets

• Composite primary key for order items

CREATE TABLE `ORDER MENUITEM` (
  `ORDER ID` INT,
  `MENUITEM ID` INT,
  QUANTITY INT,
  PRIMARY KEY (`ORDER ID`, `MENUITEM ID`),
  FOREIGN KEY (`ORDER ID`) REFERENCES `TABLE ORDER`(`ORDER ID`),
  FOREIGN KEY (`MENUITEM ID`) REFERENCES MENUITEM(`MENUITEM ID`)
);