數據庫結構

Database Schema

last updated on Nov 14, 2024

如何創建像這樣的結構圖？
How to create a structure map like this?

數據庫結構説明

Database Structure Explanation

This document explains the updated database structure for a food delivery system. It includes tables like Recipients, Delivery, Drivers, and Driver_Assignments, along with their relationships.

1. Recipients Table

This table stores recipient information, including name, address, phone, and delivery frequency.

        recipient_id (INT) PRIMARY KEY - Unique identifier for each recipient.
        name (VARCHAR) - Full name of the recipient.
        address (VARCHAR) - Address of the recipient.
        phone (VARCHAR) - Phone number of the recipient.
        zip (VARCHAR) - Postal code for the recipient.
        latitude (DECIMAL) - Latitude for mapping purposes.
        longitude (DECIMAL) - Longitude for mapping purposes.
        delivery_frequency (ENUM) - Delivery frequency ('monthly', 'bi-monthly').
        delivery_instructions (TEXT) - Specific delivery instructions.
        food_restrictions (TEXT) - Known food restrictions.
        driver_id (INT) - Foreign key referencing the responsible driver.

2. Drivers Table

This table stores delivery driver details, such as their name and contact information.

        driver_id (INT) PRIMARY KEY - Unique identifier for each driver.
        name (VARCHAR) - Full name of the driver.
        phone (VARCHAR) - Phone number of the driver.

3. Delivery Table

This table combines delivery schedule and status details, including the delivery date, status, and notes from both driver and recipient.

        delivery_id (INT) PRIMARY KEY - Unique identifier for each delivery.
        recipient_id (INT) FOREIGN KEY - Links to the Recipients table.
        driver_id (INT) FOREIGN KEY - Links to the Drivers table.
        delivery_date (DATE) - Date of the delivery.
        status (ENUM) - Status of the delivery ('pending', 'delivered', 'failed').
        driver_notes (TEXT) - Notes from the driver.
        recipient_notes (TEXT) - Notes from the recipient.

Entity Relationship Diagram (ERD)

Below are the relationships between tables:

  • The Recipients table is related to the Delivery table via recipient_id.
  • The Drivers table is linked to the Delivery and Driver_Assignments tables.
  • The Delivery table tracks each delivery's details and its connection to recipients and drivers.

Use Case Examples

Example queries:

        -- Querying a recipient's delivery details
        SELECT * FROM Recipients
        JOIN Delivery ON Recipients.recipient_id = Delivery.recipient_id
        WHERE Recipients.recipient_id = 1;
        
        -- Querying a driver's assignments
        SELECT * FROM Drivers
        JOIN Driver_Assignments ON Drivers.driver_id = Driver_Assignments.driver_id
        WHERE Drivers.driver_id = 1;
        
        -- Tracking pending deliveries
        SELECT * FROM Delivery
        WHERE status = 'pending';

數據庫命令行

Database Command

    -- Create the database (if it does not already exist)
    CREATE DATABASE IF NOT EXISTS FoodDeliveryDB;
    USE FoodDeliveryDB;
    
    -- Create Recipients table with driver_id as a foreign key and zip instead of zip_code
    CREATE TABLE Recipients (
        recipient_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        address VARCHAR(255) NOT NULL,
        phone VARCHAR(15),
        zip VARCHAR(10), -- Renamed zip_code to zip
        latitude DECIMAL(9,6),
        longitude DECIMAL(9,6),
        delivery_frequency ENUM('monthly', 'bi-monthly') NOT NULL,
        delivery_instructions TEXT,
        food_restrictions TEXT,
        driver_id INT, -- New foreign key field
        FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id)
    );
    
    -- Create Drivers table
    CREATE TABLE Drivers (
        driver_id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        phone VARCHAR(15)
    );
    
    -- Create Delivery table including delivery status, date, and notes
    CREATE TABLE Delivery (
        delivery_id INT PRIMARY KEY AUTO_INCREMENT,
        recipient_id INT NOT NULL,
        driver_id INT NOT NULL,
        delivery_date DATE NOT NULL,
        status ENUM('pending', 'delivered', 'failed') NOT NULL,
        driver_notes TEXT,
        recipient_notes TEXT,
        FOREIGN KEY (recipient_id) REFERENCES Recipients(recipient_id),
        FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id)
    );

更多

More

Fullscreen Image