CREATE TABLE chaos_orders (
    order_id INT,
    customer_name VARCHAR(20),  -- 顧客每次來(lái)都要重新登記
    milk_tea_A INT,  -- 賣到第20款奶茶怎么辦？
    price_A DECIMAL,
    milk_tea_B INT,
    price_B DECIMAL
);

-- 錯(cuò)誤姿勢(shì)：把訂單和奶茶混在一起
CREATE TABLE bad_orders (
    order_id INT,
    items VARCHAR(200)  -- "茉莉奶綠*1,芝士葡萄*2"
);
-- 正確姿勢(shì)：拆解操作臺(tái)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_time DATETIME
);
CREATE TABLE order_items (  -- 專門做奶茶的區(qū)域
    item_id INT AUTO_INCREMENT,
    order_id INT,
    milk_tea_name VARCHAR(20),
    quantity INT,
    PRIMARY KEY(item_id)
);

CREATE TABLE problem_orders (
    order_id INT,
    milk_tea_id INT,
    discount_id INT,  -- 這個(gè)優(yōu)惠屬于訂單，不是某杯奶茶！
    PRIMARY KEY(order_id, milk_tea_id)
);

-- 把會(huì)員卡專區(qū)獨(dú)立出來(lái)
ALTER TABLE orders ADD discount_id INT;  -- 優(yōu)惠屬于整個(gè)訂單
-- 保留純凈的奶茶制作區(qū)
CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT,
    order_id INT,
    milk_tea_id INT,
    quantity INT,
    PRIMARY KEY(item_id)
);

CREATE TABLE customers (
    customer_id INT,
    address VARCHAR(100),  -- "北京市海淀區(qū)xx路"
    district VARCHAR(20)  -- 這個(gè)其實(shí)可以從地址提取
);

-- 建立區(qū)域中心倉(cāng)
CREATE TABLE addresses (
    address_id INT PRIMARY KEY,
    full_address VARCHAR(100),
    district VARCHAR(20),
    city VARCHAR(20)
);
-- 分店只存提貨單號(hào)
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    address_id INT
);