编辑代码

-- 创建餐桌表
CREATE TABLE tb_table (
    table_id INT PRIMARY KEY AUTO_INCREMENT,
    table_number VARCHAR(50) NOT NULL,
    capacity INT NOT NULL,
    location VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL,
    create_time DATETIME NOT NULL,
    update_time DATETIME NOT NULL,
    is_reserved TINYINT(1) NOT NULL DEFAULT 0,
    reservation_info VARCHAR(100)
);

-- 创建顾客表
CREATE TABLE tb_customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(100),
    member_level INT NOT NULL DEFAULT 0,
    points DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    register_time DATETIME NOT NULL,
    address VARCHAR(200)
);

-- 创建订单表
CREATE TABLE tb_order (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    table_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_number VARCHAR(30) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL,
    order_time DATETIME NOT NULL,
    pay_time DATETIME,
    total_amount DECIMAL(10, 2) NOT NULL,
    discount_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    payment_method VARCHAR(20),
    remark VARCHAR(200),
    -- 外键约束
    FOREIGN KEY (table_id) REFERENCES tb_table(table_id),
    FOREIGN KEY (customer_id) REFERENCES tb_customer(customer_id)
);