design a schema for an e-commerce platform with users, products, orders

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total DECIMAL(10,2) NOT NULL,
  INDEX idx_orders_user (user_id)
);

你的数据需求
    |
    v
+-----------------------------------------------------+
| 阶段1：分析                                   |
| * 识别实体和关系               |
| * 确定访问模式（读密集型 vs 写密集型）   |
| * 根据需求选择SQL或NoSQL         |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| 阶段2：设计                                     |
| * SQL规范化到3NF，NoSQL选择嵌入/引用 |
| * 定义主键和外键              |
| * 选择合适的数据类型                     |
| * 添加约束（UNIQUE、CHECK、NOT NULL）         |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| 阶段3：优化                                   |
| * 规划索引策略                            |
| * 读密集型查询考虑反规范化   |
| * 添加时间戳（created_at、updated_at）           |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| 阶段4：迁移                                    |
| * 生成迁移脚本（升级+回滚）            |
| * 确保向后兼容                     |
| * 规划零停机部署                     |
+-----------------------------------------------------+
    |
    v
生产级可用架构

-- 错误：列中包含多个值
CREATE TABLE orders (
  id INT PRIMARY KEY,
  product_ids VARCHAR(255)  -- '101,102,103'
);

-- 正确：拆分到单独的表
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  product_id INT
);

-- 错误：customer_name仅依赖于customer_id
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  customer_name VARCHAR(100),  -- 部分依赖！
  PRIMARY KEY (order_id, product_id)
);

-- 正确：客户数据存到单独的表
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

-- 错误：country依赖于postal_code
CREATE TABLE customers (
  id INT PRIMARY KEY,
  postal_code VARCHAR(10),
  country VARCHAR(50)  -- 传递依赖！
);

-- 正确：拆分postal_codes表
CREATE TABLE postal_codes (
  code VARCHAR(10) PRIMARY KEY,
  country VARCHAR(50)
);

-- 为性能优化的反规范化设计
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total_amount DECIMAL(10,2),  -- 计算得出
  item_count INT               -- 计算得出
);

-- 合理设置长度
email VARCHAR(255)
phone VARCHAR(20)
country_code CHAR(2)

-- 金额必须使用DECIMAL
price DECIMAL(10, 2)  -- 最大支持$99,999,999.99

-- 禁止用FLOAT存储金额
price FLOAT  -- 会出现舍入误差！

DATE        -- 2025-10-31
TIME        -- 14:30:00
DATETIME    -- 2025-10-31 14:30:00
TIMESTAMP   -- 自动时区转换

-- 始终以UTC存储
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-- PostgreSQL
is_active BOOLEAN DEFAULT TRUE

-- MySQL
is_active TINYINT(1) DEFAULT 1

-- 外键索引
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- 针对查询模式的索引
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

CREATE INDEX idx_customer_status ON orders(customer_id, status);

-- 会使用索引（customer_id在前）
SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- 不会使用索引（仅用status）
SELECT * FROM orders WHERE status = 'pending';

-- 自增主键（简单场景）
id INT AUTO_INCREMENT PRIMARY KEY

-- UUID主键（分布式系统）
id CHAR(36) PRIMARY KEY DEFAULT (UUID())

-- 复合主键（关联表）
PRIMARY KEY (student_id, course_id)

FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE     -- 删除父表时同时删除子表数据
  ON DELETE RESTRICT    -- 存在引用时禁止删除父表数据
  ON DELETE SET NULL    -- 删除父表时将子表对应列设为NULL
  ON UPDATE CASCADE     -- 更新父表时同步更新子表数据

-- 唯一约束
email VARCHAR(255) UNIQUE NOT NULL

-- 复合唯一约束
UNIQUE (student_id, course_id)

-- 检查约束
price DECIMAL(10,2) CHECK (price >= 0)
discount INT CHECK (discount BETWEEN 0 AND 100)

-- 非空约束
name VARCHAR(100) NOT NULL

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id)
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INT NOT NULL,
  quantity INT NOT NULL
);

-- 关联表
CREATE TABLE enrollments (
  student_id INT REFERENCES students(id) ON DELETE CASCADE,
  course_id INT REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id, course_id)
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  manager_id INT REFERENCES employees(id)
);

-- 方式1：单独外键（完整性更强）
CREATE TABLE comments (
  id INT PRIMARY KEY,
  content TEXT NOT NULL,
  post_id INT REFERENCES posts(id),
  photo_id INT REFERENCES photos(id),
  CHECK (
    (post_id IS NOT NULL AND photo_id IS NULL) OR
    (post_id IS NULL AND photo_id IS NOT NULL)
  )
);

-- 方式2：类型+ID（更灵活，完整性较弱）
CREATE TABLE comments (
  id INT PRIMARY KEY,
  content TEXT NOT NULL,
  commentable_type VARCHAR(50) NOT NULL,
  commentable_id INT NOT NULL
);

{
  "_id": "order_123",
  "customer": {
    "id": "cust_456",
    "name": "Jane Smith",
    "email": "jane@example.com"
  },
  "items": [
    { "product_id": "prod_789", "quantity": 2, "price": 29.99 }
  ],
  "total": 109.97
}

{
  "_id": "order_123",
  "customer_id": "cust_456",
  "item_ids": ["item_1", "item_2"],
  "total": 109.97
}

// 单字段索引
db.users.createIndex({ email: 1 }, { unique: true });

// 复合索引
db.orders.createIndex({ customer_id: 1, created_at: -1 });

// 全文索引
db.articles.createIndex({ title: "text", content: "text" });

// 地理空间索引
db.stores.createIndex({ location: "2dsphere" });

-- 步骤1：添加可空列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 步骤2：部署写入新列的代码

-- 步骤3：回填现有数据
UPDATE users SET phone = '' WHERE phone IS NULL;

-- 步骤4：设置为必填（如果需要）
ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL;

-- 步骤1：添加新列
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- 步骤2：复制数据
UPDATE users SET email_address = email;

-- 步骤3：部署读取新列的代码
-- 步骤4：部署写入新列的代码

-- 步骤5：删除旧列
ALTER TABLE users DROP COLUMN email;

-- 迁移文件：YYYYMMDDHHMMSS_description.sql

-- UP（升级）
BEGIN;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
CREATE INDEX idx_users_phone ON users(phone);
COMMIT;

-- DOWN（回滚）
BEGIN;
DROP INDEX idx_users_phone ON users;
ALTER TABLE users DROP COLUMN phone;
COMMIT;

EXPLAIN SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending';

# 错误：N+1次查询
orders = db.query("SELECT * FROM orders")
for order in orders:
    customer = db.query(f"SELECT * FROM customers WHERE id = {order.customer_id}")

# 正确：单次关联查询
results = db.query("""
    SELECT orders.*, customers.name
    FROM orders
    JOIN customers ON orders.customer_id = customers.id
""")