Pizza Delivery System ERD

This Entity-Relationship Diagram (ERD) models a database for a pizza delivery service. It organizes key components of the business, including customers, orders, employees, vehicles, and pizzas, into interconnected tables. Each table holds data relevant to its category: Customers store customer details, Orders track each pizza order, Pizzas Ordered lists individual pizzas in each order, and Toppings specifies customizations. Supporting tables like Addresses, Payment Methods, and Delivery Status add further details, while reference tables for Pizza Base Types and Vehicle Types define standardized categories for consistent data.

This database enables efficient data management, allowing the pizza service to track orders, customers, delivery statuses, and even specific toppings chosen by customers. It streamlines operations by linking orders with employees, vehicles, and delivery details, ensuring that each aspect of a customer’s order—from selection to delivery—is recorded and accessible. Ultimately, this design can improve customer satisfaction by enabling fast, accurate order processing and delivery, while also providing valuable insights into customer preferences, popular pizza types, and delivery efficiency.

Description

This ERD represents a pizza delivery service database with the following main entities:

• Customers: Stores information on customers including ID, address, payment method, and other details.
• Addresses: Contains address details for both customers and employees.
• Orders: Tracks orders placed by customers, including delivery status, employees involved, and vehicle used.
• Pizzas Ordered: Lists details of pizzas included in each order.
• Toppings: Stores information on toppings added to each pizza.
• Employees: Contains information about employees who manage or deliver orders.
• Vehicles: Holds details of vehicles used for delivery, categorized by vehicle types.
• Payment Methods: Lists available payment methods like cash, credit card, etc.
• Delivery Status: Tracks the status of deliveries (e.g., completed, returned).
• Base Types: Describes the types of pizza bases (e.g., Deep Dish, Thin Crust).
• Vehicle Types: Defines the types of vehicles (e.g., bicycle, van).

SQL Data Structure

Below a SQL script to create the database structure as represented in the ERD:

CREATE TABLE Ref_Payment_Methods (
    payment_method_code VARCHAR(10) PRIMARY KEY,
    payment_method_description VARCHAR(50)
);

CREATE TABLE Ref_Delivery_Status (
    delivery_status_code VARCHAR(10) PRIMARY KEY,
    delivery_status_description VARCHAR(50)
);

CREATE TABLE Ref_Base_Types (
    base_type_code VARCHAR(10) PRIMARY KEY,
    base_type_description VARCHAR(50)
);

CREATE TABLE Ref_Vehicle_Types (
    vehicle_type_code VARCHAR(10) PRIMARY KEY,
    vehicle_type_description VARCHAR(50)
);

CREATE TABLE Addresses (
    address_id INT PRIMARY KEY,
    line_1 VARCHAR(100),
    line_2 VARCHAR(100),
    line_3 VARCHAR(100),
    line_4 VARCHAR(100),
    city VARCHAR(50),
    zip_postcode VARCHAR(10),
    state_province_county VARCHAR(50),
    country VARCHAR(50),
    other_address_details VARCHAR(255)
);

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_address_id INT,
    payment_method_code VARCHAR(10),
    customer_name VARCHAR(100),
    customer_phone VARCHAR(15),
    customer_email VARCHAR(100),
    date_of_first_order DATE,
    other_customer_details VARCHAR(255),
    FOREIGN KEY (customer_address_id) REFERENCES Addresses(address_id),
    FOREIGN KEY (payment_method_code) REFERENCES Ref_Payment_Methods(payment_method_code)
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_address_id INT,
    employee_name VARCHAR(100),
    employee_phone VARCHAR(15),
    other_employee_details VARCHAR(255),
    FOREIGN KEY (employee_address_id) REFERENCES Addresses(address_id)
);

CREATE TABLE Vehicles (
    vehicle_id INT PRIMARY KEY,
    vehicle_type_code VARCHAR(10),
    vehicle_licence_number VARCHAR(20),
    vehicle_details VARCHAR(255),
    FOREIGN KEY (vehicle_type_code) REFERENCES Ref_Vehicle_Types(vehicle_type_code)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    taken_by_employee_id INT,
    delivered_by_employee_id INT,
    delivery_status_code VARCHAR(10),
    vehicle_id INT,
    datetime_order_taken DATETIME,
    datetime_order_delivered DATETIME,
    total_order_price DECIMAL(10, 2),
    other_order_details VARCHAR(255),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (taken_by_employee_id) REFERENCES Employees(employee_id),
    FOREIGN KEY (delivered_by_employee_id) REFERENCES Employees(employee_id),
    FOREIGN KEY (delivery_status_code) REFERENCES Ref_Delivery_Status(delivery_status_code),
    FOREIGN KEY (vehicle_id) REFERENCES Vehicles(vehicle_id)
);

CREATE TABLE Pizzas_Ordered (
    order_id INT,
    pizza_sequence_number INT,
    base_type_code VARCHAR(10),
    total_pizza_price DECIMAL(10, 2),
    PRIMARY KEY (order_id, pizza_sequence_number),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (base_type_code) REFERENCES Ref_Base_Types(base_type_code)
);

CREATE TABLE Ref_Toppings (
    topping_code VARCHAR(10) PRIMARY KEY,
    topping_price DECIMAL(5, 2),
    topping_description VARCHAR(50)
);

CREATE TABLE Toppings (
    order_id INT,
    pizza_sequence_number INT,
    topping_sequence_number INT,
    topping_code VARCHAR(10),
    PRIMARY KEY (order_id, pizza_sequence_number, topping_sequence_number),
    FOREIGN KEY (order_id, pizza_sequence_number) REFERENCES Pizzas_Ordered(order_id, pizza_sequence_number),
    FOREIGN KEY (topping_code) REFERENCES Ref_Toppings(topping_code)
);