database-design

Database Design Standards Skill Pack

Design efficient and maintainable database structures.

Table Design Principles

1. Naming Conventions

Table Names：

• Lowercase letters + underscores
• Use plural form
• Example:users, order_items, user_profiles

Field Names：

• Lowercase letters + underscores
• Self-explanatory
• Example:created_at, user_id, email_address

Index Names：

-- 主键：pk_表名
PRIMARY KEY pk_users

-- 唯一索引：uk_表名_字段名
UNIQUE INDEX uk_users_email

-- 普通索引：idx_表名_字段名
INDEX idx_users_created_at

-- 外键：fk_表名_关联表名
FOREIGN KEY fk_orders_users

2. Field Type Selection

Data Type	Use Case	Example
INT/BIGINT	ID, quantity, age	user_id BIGINT
VARCHAR	Variable-length string	name VARCHAR(100)
CHAR	Fixed-length string	country_code CHAR(2)
TEXT	Long text	description TEXT
DECIMAL	Amount, precise numeric values	price DECIMAL(10,2)
DATETIME	Date and time	created_at DATETIME
ENUM	Fixed options	status ENUM('active','inactive')
BOOLEAN	Boolean value	is_deleted BOOLEAN

Note：

• Avoid using FLOAT/DOUBLE to store amounts
• Set VARCHAR length reasonably (avoid excessive length)
• Use DATETIME or TIMESTAMP consistently for time fields

3. Primary Key Design

Recommended：

-- 自增ID（适合单机）
id BIGINT AUTO_INCREMENT PRIMARY KEY

-- 雪花ID（适合分布式）
id BIGINT PRIMARY KEY COMMENT '雪花ID'

-- UUID（分布式场景）
id CHAR(36) PRIMARY KEY COMMENT 'UUID'

Avoid：

• Business fields as primary keys (such as phone numbers, email addresses)
• Composite primary keys (high complexity)

4. Foreign Key Constraints

Advantages of Using Foreign Keys：

• Data integrity assurance
• Cascading operations

Scenarios for Not Using Foreign Keys：

• High-concurrency systems (performance considerations)
• Database sharding scenarios
• Microservice architecture

-- 外键示例
FOREIGN KEY (user_id) 
  REFERENCES users(id) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE

Index Design

1. Index Types

Primary key index：

PRIMARY KEY (id)

Unique index：

UNIQUE INDEX uk_users_email (email)

Regular index：

INDEX idx_users_created_at (created_at)

Composite index(follow the leftmost prefix principle):

INDEX idx_users_name_age (name, age)
-- 可以走索引: WHERE name = 'xxx'
-- 可以走索引: WHERE name = 'xxx' AND age = 25
-- 不走索引: WHERE age = 25

Full-text index：

FULLTEXT INDEX ft_articles_content (content)

2. Index Optimization Principles

When to Create Indexes：

• ✓ Fields commonly used in WHERE clauses
• ✓ ORDER BY fields
• ✓ JOIN association fields
• ✓ Fields with high query frequency

When to Avoid Indexes：

• ✗ Fields with high data duplication (such as gender)
• ✗ Frequently updated fields
• ✗ Small tables (full table scan is faster)

Scenarios Where Indexes Become Ineffective：

-- ❌ 在索引列上使用函数
WHERE YEAR(created_at) = 2025

-- ✓ 改为
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

-- ❌ 模糊查询前导模糊
WHERE name LIKE '%张%'

-- ✓ 改为
WHERE name LIKE '张%'

-- ❌ 类型转换
WHERE user_id = '123'  -- user_id 是 INT

-- ✓ 改为
WHERE user_id = 123

SQL Optimization

1. Query Optimization

**Avoid SELECT ***:

-- ❌ 不推荐
SELECT * FROM users;

-- ✓ 推荐
SELECT id, name, email FROM users;

Use LIMIT：

SELECT id, name FROM users LIMIT 100;

Avoid N+1 queries：

-- ❌ N+1 问题
SELECT * FROM orders;  -- 查询 N 个订单
-- 然后循环查询每个订单的用户
SELECT * FROM users WHERE id = ?;

-- ✓ 使用 JOIN
SELECT o.*, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id;

2. Pagination Optimization

Traditional pagination(poor performance with large offsets):

SELECT * FROM users LIMIT 100000, 20;

Optimization solution (use ID range)：

SELECT * FROM users 
WHERE id > 100000 
ORDER BY id 
LIMIT 20;

Use covering index：

SELECT id, name FROM users 
WHERE status = 'active' 
ORDER BY created_at 
LIMIT 20;

3. COUNT Optimization

Avoid COUNT(*)：

-- ❌ 慢
SELECT COUNT(*) FROM users WHERE status = 'active';

-- ✓ 使用近似值（如缓存）
-- 或者定期统计存到另一个表

Normalization Design

First Normal Form (1NF)

Each field is an indivisible atomic value

Second Normal Form (2NF)

Eliminate partial dependencies (non-primary-key fields fully depend on the primary key)

Third Normal Form (3NF)

Eliminate transitive dependencies (non-primary-key fields do not depend on other non-primary-key fields)

Denormalization

Appropriately redundant data for performance:

-- 订单表冗余用户名（避免频繁JOIN）
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    user_name VARCHAR(100),  -- 冗余字段
    total_amount DECIMAL(10,2)
);

Common Fields

Standard Fields

id BIGINT AUTO_INCREMENT PRIMARY KEY,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
is_deleted BOOLEAN DEFAULT FALSE,
created_by BIGINT,
updated_by BIGINT

Optimistic Locking

version INT NOT NULL DEFAULT 0

Soft delete

deleted_at DATETIME NULL,
is_deleted BOOLEAN DEFAULT FALSE

Sharding and table partitioning

Vertical splitting

Split tables by business module

Horizontal splitting

-- 按 user_id 取模
user_0, user_1, user_2, ...

-- 按时间分表
order_202501, order_202502, ...

Best practices

1. Required fields：id, created_at, updated_at
2. Unified character set：utf8mb4
3. Unified time zone：UTC
4. Avoid NULL: Use NOT NULL + DEFAULT whenever possible
5. Use comments appropriately: COMMENT 'field description'
6. Regular backups: Automated backup mechanism
7. Monitor slow queries: Enable slow_query_log