ER Diagrams
Visualize 数据库设计使用 entity-关系 diagrams and notation.
你将学到
- 什么 ER diagram 是 and 为什么它 是 useful
- Entities, attributes, and 关系 in ER notation
- Crow's foot notation for cardinality
- 读取 and drawing ER diagrams
- Translating ER diagram 到 SQL 表
概念讲解
What Is an ER Diagram?
An Entity-Relationship (ER) diagram is a visual representation of a database design. It shows entities (tables), their attributes (columns), and the relationships between them. ER diagrams are the standard way to communicate a database design before writing any SQL. They help you spot missing relationships, redundant data, and normalization issues early — when changes are cheap.
Entities and Attributes
An entity is drawn as a rectangle and represents a table. Attributes are drawn as ovals connected to the entity (in Chen notation) or listed inside the rectangle (in crow's foot notation, which is more common in practice). The primary key attribute is underlined. Attributes can be marked as required (NOT NULL) or optional (nullable).
Relationships and Cardinality
A relationship is drawn as a diamond (Chen) or a line between entities (crow's foot). The cardinality — how many instances of one entity relate to another — is shown with symbols:
- One — a single line
- Many — a "crow's foot" (three-pronged fork)
- Exactly one — a line with a bar
- Zero or one — a circle and a bar
- Zero or many — a circle and a crow's foot
For example, a one-to-many relationship from User to Order is drawn with a single line at the User end and a crow's foot at the Order end.
Reading an ER Diagram
Start at one entity, follow the relationship line, and read the cardinality at each end. " 用户有许多顺序" — the crow's foot at the Order end means many. " 顺序 belongs 到精确地 one 用户" — the bar at the User end means exactly one. Many-to-many relationships in an ER diagram become junction tables in SQL.
Translating to SQL
Each entity becomes a CREATE TABLE. Each attribute becomes a column with its data type. One-to-many relationships become a foreign key on the "许多" side. Many-to-many relationships become a junction table with two foreign keys. The ER diagram is the blueprint; the SQL is the implementation.
示例代码
-- ER Diagram (text representation):
--
-- [USER] --1:N-- [ORDER] --M:N-- [PRODUCT]
-- | |
-- | +--1:N-- [ORDER_ITEM]
-- |
-- +--M:N-- [GROUP]
--
-- Translating the ER diagram to SQL:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id), -- 1:N from users
total DECIMAL(10, 2) NOT NULL DEFAULT 0
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
-- M:N between orders and products -> junction table
CREATE TABLE order_items (
order_id BIGINT NOT NULL REFERENCES orders(id),
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL,
PRIMARY KEY (order_id, product_id)
);
-- M:N between users and groups -> junction table
CREATE TABLE groups (id BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE user_groups (
user_id BIGINT NOT NULL REFERENCES users(id),
group_id BIGINT NOT NULL REFERENCES groups(id),
PRIMARY KEY (user_id, group_id)
);
文本 ER diagram showing 用户, 顺序, products, and 分组使用 1:N and M:N 关系, 然后完整 SQL translation 使用 junction 表.
动手试一试
- SQL Formatter
格式 SQL generated 从你的 ER diagram.
- JSON 到 HTML 表
Visualize entity 数据 as HTML 表 for documentation.
常见错误
错误写法
Drawing 许多-到-许多关系不使用 junction entity
正确做法
每个 M:N 关系需要 junction entity (关联表) in ER diagram. 它是 不只是行 — 它是 entity 使用它的 own attributes.
错误写法
不 marking cardinality 清晰地 on 关系行
正确做法
总是 annotate 两者都端 的关系行 使用 cardinality (1, N, 0..1, 0..N). Ambiguous 行 lead 到不正确 SQL.
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。