【系统架构设计百科】数据建模：从关系范式到文档模型的真实权衡

CREATE TABLE orders_v0 (
    order_id    BIGINT PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    items       TEXT NOT NULL,  -- "iPhone 15:2:7999,AirPods:1:999"
    address     TEXT NOT NULL,  -- "北京市海淀区中关村大街1号3单元502"
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 查询买过 AirPods 的订单：需要 LIKE 模糊匹配，无法走索引
SELECT * FROM orders_v0 WHERE items LIKE '%AirPods%';

-- 统计每个商品的销量：需要在应用层解析字符串
-- SQL 层面无法直接聚合

-- 修改商品名称（比如 "AirPods" 改为 "AirPods 4"）：
-- 需要全表扫描 + 字符串替换，极易出错
UPDATE orders_v0
SET items = REPLACE(items, 'AirPods', 'AirPods 4')
WHERE items LIKE '%AirPods%';
-- 如果有商品叫 "AirPods Pro"，这条 UPDATE 会把它改成 "AirPods 4 Pro"

CREATE TABLE orders (
    order_id    BIGINT PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id    BIGINT NOT NULL,
    product_id  BIGINT NOT NULL,
    quantity    INT NOT NULL,
    unit_price  DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE TABLE addresses (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id     BIGINT NOT NULL,
    province    VARCHAR(20) NOT NULL,
    city        VARCHAR(20) NOT NULL,
    district    VARCHAR(20) NOT NULL,
    street      VARCHAR(100) NOT NULL,
    detail      VARCHAR(200) NOT NULL
);

SELECT o.order_id, o.created_at
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.product_name = 'AirPods';

CREATE TABLE student_courses_v0 (
    student_id      BIGINT NOT NULL,
    course_id       BIGINT NOT NULL,
    student_name    VARCHAR(50) NOT NULL,
    course_name     VARCHAR(100) NOT NULL,
    department      VARCHAR(50) NOT NULL,  -- 学生所在院系
    score           DECIMAL(5,2),
    PRIMARY KEY (student_id, course_id)
);

-- 一个学生选了 10 门课，student_name 和 department 重复存了 10 遍
-- 学生转院系（department 变更），需要更新 10 行
UPDATE student_courses_v0
SET department = '计算机学院'
WHERE student_id = 2024001;
-- 如果漏更新一行，同一个学生在不同行有不同院系——数据不一致

-- 一个新课程还没有学生选，无法插入课程信息
-- 因为 student_id 是主键的一部分，不能为 NULL

CREATE TABLE students (
    student_id  BIGINT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    department  VARCHAR(50) NOT NULL
);

CREATE TABLE courses (
    course_id   BIGINT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    student_id  BIGINT NOT NULL,
    course_id   BIGINT NOT NULL,
    score       DECIMAL(5,2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE employees_v0 (
    emp_id          BIGINT PRIMARY KEY,
    emp_name        VARCHAR(50) NOT NULL,
    dept_id         BIGINT NOT NULL,
    dept_name       VARCHAR(50) NOT NULL,
    dept_location   VARCHAR(100) NOT NULL
);

CREATE TABLE departments (
    dept_id     BIGINT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    location    VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    emp_id      BIGINT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    dept_id     BIGINT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CREATE TABLE teaching_v0 (
    student_id  BIGINT NOT NULL,
    course_id   BIGINT NOT NULL,
    teacher_id  BIGINT NOT NULL,
    PRIMARY KEY (student_id, course_id)
);
-- 函数依赖：
-- (student_id, course_id) -> teacher_id
-- teacher_id -> course_id  （一个老师只教一门课）

CREATE TABLE teacher_courses (
    teacher_id  BIGINT PRIMARY KEY,
    course_id   BIGINT NOT NULL
);

CREATE TABLE student_teachers (
    student_id  BIGINT NOT NULL,
    teacher_id  BIGINT NOT NULL,
    PRIMARY KEY (student_id, teacher_id),
    FOREIGN KEY (teacher_id) REFERENCES teacher_courses(teacher_id)
);

SELECT
    p.name AS product_name,
    p.price,
    c.name AS category_name,
    b.name AS brand_name,
    s.name AS shop_name,
    pi.url AS main_image_url
FROM products p
JOIN categories c ON p.category_id = c.category_id
JOIN brands b ON p.brand_id = b.brand_id
JOIN shops s ON p.shop_id = s.shop_id
JOIN product_images pi ON p.product_id = pi.product_id AND pi.is_main = 1;

CREATE TABLE products_denormalized (
    product_id      BIGINT PRIMARY KEY,
    name            VARCHAR(200) NOT NULL,
    price           DECIMAL(10,2) NOT NULL,
    category_id     BIGINT NOT NULL,
    category_name   VARCHAR(50) NOT NULL,   -- 冗余字段
    brand_id        BIGINT NOT NULL,
    brand_name      VARCHAR(50) NOT NULL,    -- 冗余字段
    shop_id         BIGINT NOT NULL,
    shop_name       VARCHAR(50) NOT NULL,    -- 冗余字段
    main_image_url  VARCHAR(500),            -- 冗余字段
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

@Transactional
public void updateBrandName(Long brandId, String newName) {
    // 1. 更新品牌主表
    brandRepository.updateName(brandId, newName);

    // 2. 异步更新冗余字段（品牌改名频率极低，异步即可）
    eventPublisher.publish(new BrandNameChangedEvent(brandId, newName));
}

@EventListener
public void onBrandNameChanged(BrandNameChangedEvent event) {
    // 批量更新商品表中的冗余字段
    productRepository.updateBrandName(event.getBrandId(), event.getNewName());
}

SELECT
    DATE(o.created_at) AS sale_date,
    c.name AS category_name,
    SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE o.created_at >= '2025-01-01'
GROUP BY DATE(o.created_at), c.name;

CREATE TABLE daily_category_sales (
    sale_date       DATE NOT NULL,
    category_id     BIGINT NOT NULL,
    category_name   VARCHAR(50) NOT NULL,
    total_amount    DECIMAL(15,2) NOT NULL DEFAULT 0,
    order_count     INT NOT NULL DEFAULT 0,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (sale_date, category_id)
);

func updateDailyCategorySales(ctx context.Context, order Order) error {
    for _, item := range order.Items {
        _, err := db.ExecContext(ctx, `
            INSERT INTO daily_category_sales (sale_date, category_id, category_name, total_amount, order_count)
            VALUES (CURDATE(), ?, ?, ?, 1)
            ON DUPLICATE KEY UPDATE
                total_amount = total_amount + VALUES(total_amount),
                order_count = order_count + 1,
                updated_at = CURRENT_TIMESTAMP
        `, item.CategoryID, item.CategoryName, item.Amount())
        if err != nil {
            return fmt.Errorf("update daily sales: %w", err)
        }
    }
    return nil
}

CREATE TABLE order_snapshots (
    order_id            BIGINT PRIMARY KEY,
    user_name_snapshot  VARCHAR(50) NOT NULL,    -- 下单时的用户名
    address_snapshot    JSON NOT NULL,            -- 下单时的收货地址
    items_snapshot      JSON NOT NULL,            -- 下单时的商品信息（含价格）
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 关系模型：4 张表
SELECT * FROM posts WHERE id = 42;
SELECT * FROM comments WHERE post_id = 42;
SELECT * FROM tags WHERE tag_id IN (SELECT tag_id FROM post_tags WHERE post_id = 42);
SELECT * FROM authors WHERE id = (SELECT author_id FROM posts WHERE id = 42);

{
    "_id": "42",
    "title": "数据建模的权衡",
    "author": {
        "name": "张三",
        "email": "zhangsan@example.com"
    },
    "tags": ["architecture", "database", "modeling"],
    "comments": [
        {
            "user": "李四",
            "text": "写得好",
            "created_at": "2025-03-15T10:30:00Z"
        },
        {
            "user": "王五",
            "text": "第三节有点问题",
            "created_at": "2025-03-15T11:00:00Z"
        }
    ],
    "created_at": "2025-03-15T08:00:00Z"
}

{
    "_id": "product_001",
    "name": "机械键盘",
    "price": 599,
    "category": {
        "id": "cat_electronics",
        "name": "电子产品",
        "path": "数码 > 电脑外设 > 键盘"
    }
}

// 更新所有包含该分类的商品文档
db.products.updateMany(
    { "category.id": "cat_electronics" },
    {
        $set: {
            "category.name": "消费电子",
            "category.path": "数码 > 电脑外设 > 键盘"
            // 等等，path 也要改，而且不同子分类的 path 不一样...
        }
    }
);
// 这个操作在百万级商品集合上可能需要几分钟
// 而且更新过程中，部分文档已更新、部分未更新——不一致窗口

UPDATE categories SET name = '消费电子' WHERE id = 'cat_electronics';
-- 一行更新，所有引用这个分类的查询自动拿到新名字

db.orders.aggregate([
    {
        $lookup: {
            from: "users",
            localField: "user_id",
            foreignField: "_id",
            as: "user_info"
        }
    },
    {
        $lookup: {
            from: "products",
            localField: "items.product_id",
            foreignField: "_id",
            as: "product_info"
        }
    }
]);

{
    "_id": "order_20190501_001",
    "user": {
        "id": "user_123",
        "name": "张三",
        "phone": "138xxxx0001",
        "level": "VIP3",
        "referrer": {
            "id": "user_050",
            "name": "李四",
            "level": "VIP5"
        }
    },
    "items": [
        {
            "product_id": "prod_789",
            "name": "有机大米 5kg",
            "price": 89.9,
            "quantity": 2,
            "shop": {
                "id": "shop_456",
                "name": "绿色农场旗舰店",
                "commission_rate": 0.15
            }
        }
    ],
    "payment": {
        "method": "wechat",
        "amount": 179.8,
        "paid_at": "2019-05-01T10:30:00Z"
    },
    "commissions": [
        {
            "user_id": "user_050",
            "user_name": "李四",
            "amount": 26.97,
            "level": 1
        },
        {
            "user_id": "user_020",
            "user_name": "王五",
            "amount": 8.99,
            "level": 2
        }
    ]
}

// 用户 user_123 从 VIP3 升级到 VIP4
// 需要更新：
// 1. 所有 user_123 的订单文档中的 user.level
// 2. 所有 user_123 作为推荐人的订单文档中的 user.referrer.level
// 3. 所有 user_123 在佣金列表中的记录

// 第一类更新
db.orders.updateMany(
    { "user.id": "user_123" },
    { $set: { "user.level": "VIP4" } }
);

// 第二类更新
db.orders.updateMany(
    { "user.referrer.id": "user_123" },
    { $set: { "user.referrer.level": "VIP4" } }
);

// 第三类更新——这个最麻烦，需要操作嵌套数组
db.orders.updateMany(
    { "commissions.user_id": "user_123" },
    { $set: { "commissions.$[elem].level_at_update": "VIP4" } },
    { arrayFilters: [{ "elem.user_id": "user_123" }] }
);

db.orders.aggregate([
    { $match: { "payment.paid_at": { $gte: ISODate("2019-05-01"), $lt: ISODate("2019-06-01") } } },
    { $unwind: "$commissions" },
    { $group: {
        _id: "$commissions.user_id",
        total_commission: { $sum: "$commissions.amount" },
        order_count: { $sum: 1 }
    }},
    { $sort: { total_commission: -1 } },
    { $limit: 100 }
]);

CREATE TABLE orders (
    order_id    VARCHAR(30) PRIMARY KEY,
    user_id     VARCHAR(20) NOT NULL REFERENCES users(user_id),
    referrer_id VARCHAR(20) REFERENCES users(user_id),
    total_amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(20) NOT NULL,
    paid_at     TIMESTAMP,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id          SERIAL PRIMARY KEY,
    order_id    VARCHAR(30) NOT NULL REFERENCES orders(order_id),
    product_id  VARCHAR(20) NOT NULL REFERENCES products(product_id),
    shop_id     VARCHAR(20) NOT NULL REFERENCES shops(shop_id),
    quantity    INT NOT NULL,
    unit_price  DECIMAL(10,2) NOT NULL,
    -- 快照字段：下单时刻的商品名和价格
    product_name_snapshot VARCHAR(200) NOT NULL,
    unit_price_snapshot   DECIMAL(10,2) NOT NULL
);

CREATE TABLE commissions (
    id          SERIAL PRIMARY KEY,
    order_id    VARCHAR(30) NOT NULL REFERENCES orders(order_id),
    user_id     VARCHAR(20) NOT NULL REFERENCES users(user_id),
    amount      DECIMAL(10,2) NOT NULL,
    level       INT NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_commissions_user_created ON commissions(user_id, created_at);

WITH RECURSIVE friends_of_friends AS (
    -- 第一层：Alice 的直接关注
    SELECT
        f.target_user_id AS user_id,
        1 AS depth
    FROM follows f
    WHERE f.source_user_id = (SELECT user_id FROM users WHERE name = 'Alice')

    UNION

    -- 第二层：朋友的关注
    SELECT
        f.target_user_id AS user_id,
        fof.depth + 1 AS depth
    FROM follows f
    JOIN friends_of_friends fof ON f.source_user_id = fof.user_id
    WHERE fof.depth < 2
)
SELECT DISTINCT u.name
FROM friends_of_friends fof
JOIN users u ON fof.user_id = u.user_id
-- 过滤：这些人中谁关注了 Charlie
WHERE EXISTS (
    SELECT 1 FROM follows f2
    WHERE f2.source_user_id = fof.user_id
    AND f2.target_user_id = (SELECT user_id FROM users WHERE name = 'Charlie')
)
AND u.name NOT IN ('Alice', 'Charlie');

CREATE TABLE follows (
    source_user_id BIGINT NOT NULL,
    target_user_id BIGINT NOT NULL,
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (source_user_id, target_user_id),
    INDEX idx_target (target_user_id, source_user_id)
);

-- Alice 的朋友的朋友
SELECT DISTINCT f2.target_user_id
FROM follows f1
JOIN follows f2 ON f1.target_user_id = f2.source_user_id
WHERE f1.source_user_id = 123  -- Alice 的 user_id
AND f2.target_user_id <> 123;

CREATE TABLE category_closure (
    ancestor_id     BIGINT NOT NULL,
    descendant_id   BIGINT NOT NULL,
    depth           INT NOT NULL,
    PRIMARY KEY (ancestor_id, descendant_id),
    INDEX idx_descendant (descendant_id, ancestor_id)
);

-- 查询某分类下的所有子分类（任意层级）：O(1) 复杂度
SELECT descendant_id FROM category_closure
WHERE ancestor_id = 100 AND depth > 0;

-- 查询某分类的所有上级分类：O(1) 复杂度
SELECT ancestor_id FROM category_closure
WHERE descendant_id = 200 AND depth > 0
ORDER BY depth;

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    path        ltree NOT NULL
);

CREATE INDEX idx_path_gist ON categories USING GIST (path);

-- 插入数据
INSERT INTO categories (name, path) VALUES
    ('电子产品', 'root.electronics'),
    ('手机', 'root.electronics.phones'),
    ('苹果手机', 'root.electronics.phones.apple'),
    ('安卓手机', 'root.electronics.phones.android');

-- 查询"电子产品"下的所有子分类
SELECT * FROM categories
WHERE path <@ 'root.electronics';

-- 查询"苹果手机"的所有上级分类
SELECT * FROM categories
WHERE path @> 'root.electronics.phones.apple';

-- 用户表
CREATE TABLE users (
    user_id     BIGINT AUTO_INCREMENT PRIMARY KEY,
    username    VARCHAR(50) NOT NULL UNIQUE,
    email       VARCHAR(100) NOT NULL UNIQUE,
    phone       VARCHAR(20),
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 商品表
CREATE TABLE products (
    product_id  BIGINT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    description TEXT,
    price       DECIMAL(10,2) NOT NULL,
    stock       INT NOT NULL DEFAULT 0,
    category_id BIGINT NOT NULL,
    shop_id     BIGINT NOT NULL,
    status      TINYINT NOT NULL DEFAULT 1,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    FOREIGN KEY (shop_id) REFERENCES shops(shop_id)
) ENGINE=InnoDB;

-- 分类表（邻接列表模型）
CREATE TABLE categories (
    category_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    parent_id   BIGINT,
    level       TINYINT NOT NULL DEFAULT 1,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id)
) ENGINE=InnoDB;

-- 订单表
CREATE TABLE orders (
    order_id    BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    address_id  BIGINT NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (address_id) REFERENCES addresses(address_id)
) ENGINE=InnoDB;

-- 订单明细表
CREATE TABLE order_items (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id    BIGINT NOT NULL,
    product_id  BIGINT NOT NULL,
    quantity    INT NOT NULL,
    unit_price  DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB;

-- 地址表
CREATE TABLE addresses (
    address_id  BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    province    VARCHAR(20) NOT NULL,
    city        VARCHAR(20) NOT NULL,
    district    VARCHAR(20) NOT NULL,
    detail      VARCHAR(200) NOT NULL,
    phone       VARCHAR(20) NOT NULL,
    receiver    VARCHAR(50) NOT NULL,
    is_default  TINYINT NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB;

type OrderDetail struct {
    OrderID    int64           `db:"order_id"`
    UserName   string          `db:"username"`
    TotalAmount decimal.Decimal `db:"total_amount"`
    Status     string          `db:"status"`
    Receiver   string          `db:"receiver"`
    Address    string          `db:"full_address"`
    Items      []OrderItemDetail
    CreatedAt  time.Time       `db:"created_at"`
}

func (r *OrderRepo) GetOrderDetail(ctx context.Context, orderID int64) (*OrderDetail, error) {
    query := `
        SELECT o.order_id, u.username, o.total_amount, o.status,
               a.receiver,
               CONCAT(a.province, a.city, a.district, a.detail) AS full_address,
               o.created_at
        FROM orders o
        JOIN users u ON o.user_id = u.user_id
        JOIN addresses a ON o.address_id = a.address_id
        WHERE o.order_id = ?
    `
    var detail OrderDetail
    if err := r.db.GetContext(ctx, &detail, query, orderID); err != nil {
        return nil, fmt.Errorf("get order detail: %w", err)
    }

    itemsQuery := `
        SELECT oi.product_id, p.name AS product_name, oi.quantity, oi.unit_price
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        WHERE oi.order_id = ?
    `
    if err := r.db.SelectContext(ctx, &detail.Items, itemsQuery, orderID); err != nil {
        return nil, fmt.Errorf("get order items: %w", err)
    }

    return &detail, nil
}

ALTER TABLE orders
    ADD COLUMN receiver_snapshot VARCHAR(50) NOT NULL DEFAULT '',
    ADD COLUMN address_snapshot JSON NOT NULL DEFAULT '{}';

-- 新建订单时，把当前地址快照写入
-- 旧的 address_id 字段保留，但不再用于展示订单地址

func (s *OrderService) CreateOrder(ctx context.Context, req CreateOrderRequest) (*Order, error) {
    // 获取当前地址
    addr, err := s.addressRepo.GetByID(ctx, req.AddressID)
    if err != nil {
        return nil, fmt.Errorf("get address: %w", err)
    }

    // 创建订单时拍快照
    order := &Order{
        UserID:           req.UserID,
        TotalAmount:      calculateTotal(req.Items),
        Status:           "pending",
        AddressID:        req.AddressID,
        ReceiverSnapshot: addr.Receiver,
        AddressSnapshot: AddressSnapshot{
            Province: addr.Province,
            City:     addr.City,
            District: addr.District,
            Detail:   addr.Detail,
            Phone:    addr.Phone,
        },
    }

    return s.orderRepo.Create(ctx, order)
}

ALTER TABLE products
    ADD COLUMN category_name VARCHAR(50) NOT NULL DEFAULT '',
    ADD COLUMN brand_name VARCHAR(50) NOT NULL DEFAULT '';

-- 一次性回填
UPDATE products p
JOIN categories c ON p.category_id = c.category_id
SET p.category_name = c.name;

-- 通过事件机制保持同步（分类改名时触发更新）

-- 热数据表：最近 3 个月的订单
CREATE TABLE orders_hot (
    -- 结构同 orders 表
) ENGINE=InnoDB;

-- 冷数据表：3 个月前的订单（按年分表）
CREATE TABLE orders_archive_2025 (
    -- 结构同 orders 表
) ENGINE=InnoDB;

-- 应用层路由逻辑

func (r *OrderRepo) GetUserOrders(ctx context.Context, userID int64, page, size int) ([]Order, error) {
    // 先查热表
    orders, err := r.queryOrders(ctx, "orders_hot", userID, page, size)
    if err != nil {
        return nil, err
    }
    if len(orders) >= size {
        return orders, nil
    }

    // 热表不够，补查归档表
    remaining := size - len(orders)
    archiveOrders, err := r.queryOrders(ctx, "orders_archive_2025", userID, 1, remaining)
    if err != nil {
        return nil, err
    }

    return append(orders, archiveOrders...), nil
}

type ProductSearchDoc struct {
    ProductID    int64    `json:"product_id"`
    Name         string   `json:"name"`
    Description  string   `json:"description"`
    Price        float64  `json:"price"`
    CategoryName string   `json:"category_name"`
    CategoryPath []string `json:"category_path"`  // ["电子产品", "手机", "苹果手机"]
    BrandName    string   `json:"brand_name"`
    ShopName     string   `json:"shop_name"`
    SalesCount   int64    `json:"sales_count"`
    Rating       float64  `json:"rating"`
    Tags         []string `json:"tags"`
    CreatedAt    string   `json:"created_at"`
}

func (s *SearchSyncService) OnProductUpdated(event ProductUpdatedEvent) error {
    doc := s.buildSearchDoc(event.ProductID)
    _, err := s.esClient.Index().
        Index("products").
        Id(fmt.Sprintf("%d", event.ProductID)).
        BodyJson(doc).
        Do(context.Background())
    return err
}

-- 找出和用户 123 买过相同商品的其他用户，然后推荐他们买的其他商品
SELECT
    oi2.product_id,
    COUNT(DISTINCT oi2.order_id) AS purchase_count
FROM order_items oi1
JOIN orders o1 ON oi1.order_id = o1.order_id AND o1.user_id = 123
JOIN order_items oi_same ON oi1.product_id = oi_same.product_id AND oi_same.id <> oi1.id
JOIN orders o_same ON oi_same.order_id = o_same.order_id AND o_same.user_id <> 123
JOIN order_items oi2 ON o_same.order_id = oi2.order_id
WHERE oi2.product_id NOT IN (
    SELECT product_id FROM order_items
    JOIN orders ON order_items.order_id = orders.order_id
    WHERE orders.user_id = 123
)
GROUP BY oi2.product_id
ORDER BY purchase_count DESC
LIMIT 20;

db.createCollection("orders", {
    validator: {
        $jsonSchema: {
            bsonType: "object",
            required: ["user_id", "items", "total_amount", "status"],
            properties: {
                user_id: {
                    bsonType: "string",
                    description: "必填字段，用户 ID"
                },
                items: {
                    bsonType: "array",
                    minItems: 1,
                    items: {
                        bsonType: "object",
                        required: ["product_id", "quantity", "price"],
                        properties: {
                            product_id: { bsonType: "string" },
                            quantity: { bsonType: "int", minimum: 1 },
                            price: { bsonType: "decimal" }
                        }
                    }
                },
                total_amount: { bsonType: "decimal" },
                status: { enum: ["pending", "paid", "shipped", "completed", "cancelled"] }
            }
        }
    }
});

-- PostgreSQL JSONB：在关系表中使用文档模型
CREATE TABLE events (
    event_id    BIGSERIAL PRIMARY KEY,
    event_type  VARCHAR(50) NOT NULL,
    payload     JSONB NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 在 JSONB 字段上建索引
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- 查询 JSONB 字段
SELECT * FROM events
WHERE payload @> '{"user_id": "user_123", "action": "purchase"}'
AND created_at >= '2025-01-01';

-- OLAP 星型模型示例（适合数据仓库，不适合 OLTP）
CREATE TABLE fact_sales (
    sale_id         BIGINT PRIMARY KEY,
    date_key        INT NOT NULL,       -- 维度外键
    product_key     INT NOT NULL,       -- 维度外键
    customer_key    INT NOT NULL,       -- 维度外键
    store_key       INT NOT NULL,       -- 维度外键
    quantity        INT NOT NULL,
    unit_price      DECIMAL(10,2) NOT NULL,
    total_amount    DECIMAL(12,2) NOT NULL,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0
);

-- 日期维度表（预计算的、反范式化的）
CREATE TABLE dim_date (
    date_key        INT PRIMARY KEY,
    full_date       DATE NOT NULL,
    year            INT NOT NULL,
    quarter         INT NOT NULL,
    month           INT NOT NULL,
    week            INT NOT NULL,
    day_of_week     VARCHAR(10) NOT NULL,
    is_weekend      BOOLEAN NOT NULL,
    is_holiday      BOOLEAN NOT NULL,
    fiscal_year     INT NOT NULL,
    fiscal_quarter  INT NOT NULL
);