数据库设计
计划数据库从 需求到 Schema: entities, attributes, and 关系.
你将学到
- 数据库设计处理: 需求到 Schema
- Identifying entities and attributes 从需求
- 选择主要键 and 数据类型 wisely
- 设计 for 查询模式, 不只是数据 shape
- 常见设计模式 and anti-模式
概念讲解
The Design Process
Good database design follows a process: (1) understand the requirements and what questions the database must answer, (2) identify the entities (nouns: users, orders, products), (3) identify relationships between entities, (4) choose attributes and data types for each entity, (5) normalize to reduce redundancy, (6) add indexes based on expected queries, and (7) review and iterate.
Identifying Entities and Attributes
Read the requirements and highlight the nouns — these are your entities. A "用户位置顺序 for products" requirement gives you three entities: users, orders, products. Each entity becomes a table. Attributes are the properties: a user has a name, email, and created_at. Choose data types carefully — use DECIMAL for money, TIMESTAMP for dates, VARCHAR with sensible lengths for strings.
Choosing Primary Keys
Every table needs a primary key. The best choice is a surrogate key — an auto-incrementing integer (SERIAL/BIGSERIAL) or a UUID. Avoid using natural keys (like email or username) as primary keys — they can change, and changing a primary key cascades to every foreign key that references it. Surrogate keys are stable, compact, and fast to index.
Designing for Query Patterns
Do not just model the data — model the queries. What will the application ask most often? "Show 用户's recent 顺序" means you need an index on (user_id, created_at). "搜索 products 通过 name" means you need a full-text index or a LIKE with an index. Design indexes after you know the queries, not before. Consider read-heavy vs write-heavy patterns and denormalize selectively for hot paths.
Anti-Patterns to Avoid
- EAV (Entity-Attribute-Value) — storing attributes as rows in a key-value table. It seems flexible but makes queries impossibly complex.
- God table — one massive table with 100+ columns. Split into related tables.
- Polymorphic associations — a foreign key that points to different tables depending on a type column. Use separate foreign keys or a proper association table instead.
- Storing arrays as CSV — always use a junction table or a native array type.
示例代码
-- A well-designed e-commerce schema (simplified)
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(200) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
category VARCHAR(100) NOT NULL,
in_stock BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
total DECIMAL(10, 2) NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL REFERENCES orders(id),
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);
-- Indexes based on expected queries
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
CREATE INDEX idx_products_category ON products (category);
clean e-commerce Schema 使用 surrogate 键, proper foreign 键, junction 表 for 顺序项, and 索引设计 for 常见查询模式.
动手试一试
- SQL Formatter
格式你的完整 Schema 定义 for review.
- Docker Compose Generator
Spin 上数据库容器到 测试你的 Schema.
常见错误
错误写法
设计 Schema 之前理解查询
正确做法
Talk 到 stakeholders 关于什么问题数据库必须答案. 设计表 到持有数据, 然后设计索引到 serve 查询 efficiently.
错误写法
Using natural 键 (email, 用户名) as 主要键
正确做法
Natural 键能 更改, and 更改 PK cascades 到每个 FK. Use surrogate 键 (SERIAL or UUID) 那个从不更改 and 是快 到索引.
练习 数据库设计
1 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。