data-design

数据设计

概述

有关设计高效、可维护且可扩展的数据结构、模式和流程的原则。

---

数据建模

实体关系图

┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│    User     │       │    Order    │       │   Product   │
├─────────────┤       ├─────────────┤       ├─────────────┤
│ id (PK)     │──┐    │ id (PK)     │    ┌──│ id (PK)     │
│ email       │  │    │ user_id(FK) │←───┘  │ name        │
│ name        │  └───→│ status      │       │ price       │
│ created_at  │       │ total       │       │ stock       │
└─────────────┘       │ created_at  │       └─────────────┘
                      └─────────────┘              │
                             │                     │
                      ┌──────┴──────┐              │
                      ↓             ↓              │
               ┌─────────────┐                     │
               │ OrderItem   │                     │
               ├─────────────┤                     │
               │ id (PK)     │                     │
               │ order_id(FK)│                     │
               │ product_id  │─────────────────────┘
               │ quantity    │
               │ price       │
               └─────────────┘

关系类型

类型	描述	示例
1:1	一对一	User ↔ Profile
1:N	一对多	User → Orders
M:N	多对多	Students ↔ Courses

-- 1:1 (profile extends user)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

CREATE TABLE profiles (
  user_id INTEGER PRIMARY KEY REFERENCES users(id),
  bio TEXT,
  avatar_url VARCHAR(255)
);

-- 1:N (user has many orders)
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total DECIMAL(10,2)
);

-- M:N (students ↔ courses via junction table)
CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course_id INTEGER REFERENCES courses(id),
  enrolled_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (student_id, course_id)
);

---

规范化

范式

范式	规则	违规示例
1NF	原子值，无重复组	tags: "a,b,c"
2NF	1NF + 无部分依赖	非键依赖于复合键的一部分
3NF	2NF + 无传递依赖	orders 表中的 zip → city
BCNF	每个决定因素都是候选键	罕见的边缘情况

-- ❌ Violates 1NF (non-atomic)
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  tags VARCHAR(255)  -- "electronics,sale,featured"
);

-- ✅ 1NF compliant
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE product_tags (
  product_id INTEGER REFERENCES products(id),
  tag VARCHAR(50),
  PRIMARY KEY (product_id, tag)
);

-- ❌ Violates 3NF (transitive dependency)
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_zip VARCHAR(10),
  customer_city VARCHAR(100)  -- Depends on zip, not order
);

-- ✅ 3NF compliant
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  zip VARCHAR(10),
  city VARCHAR(100)
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id)
);

---

反规范化

何时反规范化

Normalize for:
✅ Write-heavy workloads
✅ Data integrity requirements
✅ Storage efficiency
✅ Flexibility in queries

Denormalize for:
✅ Read-heavy workloads
✅ Complex joins hurting performance
✅ Reporting/analytics
✅ Known access patterns

反规范化模式

-- Computed columns
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  items JSONB,
  item_count INTEGER GENERATED ALWAYS AS (jsonb_array_length(items)) STORED,
  total DECIMAL(10,2)
);

-- Duplicated data for read performance
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INTEGER REFERENCES users(id),
  author_name VARCHAR(100),  -- Duplicated from users
  author_avatar VARCHAR(255), -- Duplicated from users
  content TEXT
);

-- Materialized view for complex queries
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
  DATE_TRUNC('month', created_at) as month,
  product_id,
  SUM(quantity) as units_sold,
  SUM(total) as revenue
FROM order_items
GROUP BY 1, 2;

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

---

模式设计模式

软删除

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255),
  deleted_at TIMESTAMP NULL,
  -- Partial unique index
  CONSTRAINT unique_active_email UNIQUE (email) WHERE deleted_at IS NULL
);

-- Query active users only
SELECT * FROM users WHERE deleted_at IS NULL;

审计追踪

CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  table_name VARCHAR(100),
  record_id INTEGER,
  action VARCHAR(10),  -- INSERT, UPDATE, DELETE
  old_data JSONB,
  new_data JSONB,
  changed_by INTEGER REFERENCES users(id),
  changed_at TIMESTAMP DEFAULT NOW()
);

-- Trigger for automatic auditing
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END,
    current_setting('app.user_id', true)::INTEGER
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

多租户

-- Row-level security
CREATE TABLE organizations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  org_id INTEGER REFERENCES organizations(id),
  name VARCHAR(255)
);

-- Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY org_isolation ON projects
  USING (org_id = current_setting('app.org_id')::INTEGER);

-- Set org context per request
SET app.org_id = 123;
SELECT * FROM projects; -- Only sees org 123's projects

版本控制 / 历史记录

-- Version table pattern
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  current_version_id INTEGER
);

CREATE TABLE document_versions (
  id SERIAL PRIMARY KEY,
  document_id INTEGER REFERENCES documents(id),
  version INTEGER,
  content TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  created_by INTEGER REFERENCES users(id),
  UNIQUE (document_id, version)
);

-- Temporal tables (PostgreSQL)
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  price DECIMAL(10,2),
  valid_from TIMESTAMP DEFAULT NOW(),
  valid_to TIMESTAMP DEFAULT 'infinity'
);

-- Query historical state
SELECT * FROM products
WHERE valid_from <= '2024-01-01' AND valid_to > '2024-01-01';

---

NoSQL 模式设计

文档存储（MongoDB）

// Embedded vs Referenced

// ✅ Embed when: data is accessed together, 1:few relationship
{
  _id: ObjectId("..."),
  title: "Blog Post",
  author: {
    name: "John",
    email: "[email protected]"
  },
  comments: [
    { user: "Jane", text: "Great post!", date: ISODate("...") }
  ]
}

// ✅ Reference when: data is accessed independently, 1:many or M:N
{
  _id: ObjectId("..."),
  title: "Blog Post",
  authorId: ObjectId("..."),  // Reference to users collection
  commentIds: [ObjectId("..."), ObjectId("...")]
}

// ❌ Anti-pattern: Unbounded arrays
{
  _id: ObjectId("..."),
  logs: [...] // Can grow to millions, hits 16MB limit
}

// ✅ Better: Bucket pattern
{
  _id: ObjectId("..."),
  sensorId: "sensor-123",
  date: ISODate("2024-01-15"),
  readings: [...] // Max ~1000 per document
}

键值存储（Redis）

# Naming conventions
user:123              # User object
user:123:sessions     # User's sessions (set)
user:123:orders       # User's orders (list)
order:456             # Order object
orders:pending        # Queue of pending orders (list)
products:category:electronics  # Products in category (set)

# Expiration patterns
session:{token}       # Expires after 30 min
rate_limit:ip:1.2.3.4 # Expires after 1 min
cache:api:/users/123  # Expires after 5 min

---

数据管道设计

ETL 与 ELT

ETL (Extract, Transform, Load):
Source → Transform (external) → Data Warehouse
Use: Traditional, when transformation is complex

ELT (Extract, Load, Transform):
Source → Data Lake/Warehouse → Transform (in-place)
Use: Modern, leverages warehouse compute power

事件溯源

// Events are the source of truth
interface Event {
  id: string;
  aggregateId: string;
  type: string;
  payload: unknown;
  timestamp: Date;
  version: number;
}

// Event store
class EventStore {
  async append(aggregateId: string, events: Event[]) {
    await db.events.insertMany(events);
  }

  async getEvents(aggregateId: string): Promise<Event[]> {
    return db.events
      .find({ aggregateId })
      .sort({ version: 1 })
      .toArray();
  }
}

// Rebuild state from events
function rebuildAccount(events: Event[]): Account {
  return events.reduce((account, event) => {
    switch (event.type) {
      case 'AccountOpened':
        return { balance: 0, ...event.payload };
      case 'MoneyDeposited':
        return { ...account, balance: account.balance + event.payload.amount };
      case 'MoneyWithdrawn':
        return { ...account, balance: account.balance - event.payload.amount };
      default:
        return account;
    }
  }, {} as Account);
}

---

数据治理

数据质量维度

维度	描述	示例检查
准确性	正确的值	邮箱格式验证
完整性	无缺失数据	必填字段存在
一致性	跨系统一致	所有表中的用户 ID 匹配
及时性	保持最新	最后更新时间在 24 小时内
唯一性	无重复	每个用户的邮箱唯一

模式演进

-- Safe migrations

-- ✅ Adding nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;

-- ✅ Adding column with default
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- ⚠️ Making column non-null (multi-step)
-- Step 1: Add with default
ALTER TABLE users ADD COLUMN verified BOOLEAN DEFAULT false;
-- Step 2: Backfill data
UPDATE users SET verified = true WHERE email_verified_at IS NOT NULL;
-- Step 3: Add constraint
ALTER TABLE users ALTER COLUMN verified SET NOT NULL;

-- ❌ Dangerous: Renaming column
-- Instead: Add new, migrate data, remove old (over multiple deploys)

---

相关技能

• [[database]] - 数据库实现
• [[architecture-patterns]] - 数据架构模式
• [[api-design]] - API 中的数据