database-design

MySQL Database Design Skill

This Skill provides reusable MySQL table structure templates and design specifications, covering fixed fields, status fields, unique constraints, time fields, index design, and cross-table relationship modeling.

Reference files:

• references/index-design-principles.md — Index design details and EXPLAIN guide
• references/status-field-patterns.md — Multilingual enumeration examples and state machine patterns
• examples/multi-table-design.md — Order-order item-payment cross-table design example

---

1) General specifications

Fixed fields (must be included in all business tables)

Field name	Type	Description
id	bigint	Primary key ID; it is recommended to use Snowflake or another distributed ID scheme
deleted	tinyint	Soft delete flag,0 indicates not deleted,1 indicates deleted
create_time	timestamp(6)	Creation time
update_time	timestamp(6)	Update time

Business unique identifier field

• Name it according to entity semantics, for example order_id、payment_id、wallet_id; do not use xx_id as a formal field name
• A unique constraint is usually required, used for idempotency, duplicate prevention, and integration with external systems
• Use varchar(32) ~ varchar(64)for internal high-frequency associations bigint

Status field specifications

• Use numeric status codes by default (tinyint is preferred; when there are many statuses, use smallint）
• The application layer maintains semantics through enums; do not use display text as the database core status value
• If the original external status needs to be retained, add a raw_status / channel_status string field
• Field names are recommended to be status or biz_status; the meaning must be clearly stated in the comment

status tinyint NOT NULL COMMENT '状态: 0-初始化, 1-处理中, 2-成功, 3-失败, 4-已取消'

Application layer enum example (see the full version in references/status-field-patterns.md）：

public enum OrderStatus {
    INIT(0), PROCESSING(1), SUCCESS(2), FAILED(3), CANCELLED(4);
    private final int code;
    OrderStatus(int code) { this.code = code; }
    public int getCode() { return code; }
}

Soft delete field specifications

• deleted must be NOT NULL DEFAULT 0, with the comment clearly stating 0-未删除, 1-已删除
• When audit requirements are high, add delete_time to record the deletion time

deleted tinyint NOT NULL DEFAULT 0 COMMENT '删除标记: 0-未删除, 1-已删除'

Time field specifications

• Business time fields uniformly use timestamp(6)(create_time, update_time, biz_time, etc.)
• "Setting-type time" (only expressing hours, minutes, seconds, cycle configuration, etc.) is not required to use timestamp(6); choose the appropriate type according to semantics
• Microsecond precision cannot replace concurrency control mechanisms—concurrency control should be solved through unique constraints, optimistic locking, transaction isolation, or state machine constraints

Unique constraint specifications

• Idempotency, duplicate prevention, and deduplication should preferentially rely on database unique constraints as a fallback; do not rely only on application-layer checks
• Unique index fields should be as stable as possible; avoid including frequently changing fields in unique keys
• When multiple columns together form a business unique key, explicitly design a composite unique index

---

2) General business table example

CREATE TABLE `t_order` (
  `id` bigint NOT NULL COMMENT '主键 ID',
  `order_id` varchar(64) NOT NULL COMMENT '业务订单 ID',

  `user_id` bigint NOT NULL COMMENT '用户 ID',
  `status` tinyint NOT NULL COMMENT '状态: 0-初始化, 1-处理中, 2-成功, 3-失败, 4-已取消',
  `channel_status` varchar(32) DEFAULT NULL COMMENT '渠道原始状态',
  `amount` decimal(18,2) NOT NULL COMMENT '订单金额',
  `biz_time` timestamp(6) DEFAULT NULL COMMENT '业务发生时间',

  `deleted` tinyint NOT NULL DEFAULT 0 COMMENT '删除标记: 0-未删除, 1-已删除',
  `create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
  `update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',

  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_order_id` (`order_id`) USING BTREE,

  KEY `idx_user_deleted_ct` (`user_id`, `deleted`, `create_time`) USING BTREE,
  KEY `idx_status_deleted_ut` (`status`, `deleted`, `update_time`) USING BTREE,
  KEY `idx_biz_time` (`biz_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

Description:

• order_id is the business unique identifier, responsible for idempotency and external integration
• status stores the status code,channel_status stores the channel's original status
• The index is only a general reference; the final decision must be confirmed in combination with real SQL and the execution plan

---

3) Index design principles

Basic principles

• Design composite index order comprehensively based on WHERE equality conditions, range conditions, sort fields, and selectivity
• Do not mechanically apply templates; you must combine core SQL with EXPLAIN to confirm
• Retaining 2~5 high-hit indexes is sufficient to avoid excessively high write costs
• deleted Whether to place it as a prefix of a composite index must be determined in conjunction with the data distribution (when selectivity is extremely low, it does not necessarily have to be placed first)

Common Query Patterns

Query Type	SQL Pattern	Recommended Index
Idempotency/External Integration	WHERE order_id = ?	UNIQUE (order_id)
List Query	WHERE user_id = ? AND deleted = 0 ORDER BY create_time DESC	(user_id, deleted, create_time)
Status Scan	WHERE status IN (...) AND deleted = 0 ORDER BY update_time	(status, deleted, update_time)
Time Range	WHERE biz_time BETWEEN ? AND ? AND deleted = 0	(biz_time, deleted) or (deleted, biz_time)

For detailed index design notes (including EXPLAIN interpretation, composite index rules, covering indexes, and common pitfalls), see references/index-design-principles.md。

---

4) Blank Business Table Template

CREATE TABLE `t_xx_demo` (
  `id` bigint NOT NULL COMMENT '主键 ID',
  `demo_id` varchar(64) NOT NULL COMMENT '业务唯一标识',

  `status` tinyint NOT NULL COMMENT '状态码，含义需在注释或设计文档中说明',
  `raw_status` varchar(32) DEFAULT NULL COMMENT '外部原始状态，可选',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',

  `deleted` tinyint NOT NULL DEFAULT 0 COMMENT '删除标记: 0-未删除, 1-已删除',
  `create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
  `update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',

  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_demo_id` (`demo_id`) USING BTREE,
  KEY `idx_status_deleted_ut` (`status`, `deleted`, `update_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='XX 业务表示例';

---

5) Advanced Topics: Partitioning and Sharding

Table Partitioning (MySQL Partitioned Tables)

Applicable scenarios: a single table has a large amount of data (usually > 500GB) and has an obvious partition key.

CREATE TABLE `t_order_partitioned` (
  `id` bigint NOT NULL,
  `order_id` varchar(64) NOT NULL,
  `user_id` bigint NOT NULL,
  `create_time` timestamp(6) NOT NULL,
  PRIMARY KEY (`id`, `create_time`),
  UNIQUE KEY `uk_order_id` (`order_id`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(create_time)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

Notes:

• The partition key must be included in the primary key and unique indexes (MySQL limitation)
• Partitioning cannot completely replace index optimization, and improper partitioning may instead degrade performance
• It is recommended to optimize with indexes first, then evaluate whether to introduce partitioning

Sharding

Consider sharding when a single table reaches hundreds of millions of rows and neither index optimization nor partitioning can satisfy the requirements:

• Vertical Sharding: split into different databases by business module (such as an order database and a user database)
• Horizontal Sharding: distribute data across multiple database instances by shard key (such as user_id)

Sharding introduces complexity such as distributed transactions, cross-shard queries, and global primary keys,and should be used as a last resort, prioritizing resolution through index optimization, caching, and archiving.

For an example of cross-table relationship design (order-order item-payment model), see examples/multi-table-design.md。

---

6) Quick Start

1. Copy the blank template in Section 4 as a starting point
2. Replace the table name and the business unique identifier field name (such as t_payment_order、payment_id）
3. Add business fields, and add clear comments for core fields
4. Design unique constraints and regular indexes based on the core query SQL
5. Use EXPLAIN to validate key SQL, and adjust the composite index order if necessary
6. If cross-table relationships are involved, refer to examples/multi-table-design.md

Naming Suggestions

• Table name:t_ + business module + entity name, such as t_payment_order
• Business unique identifier: name it according to entity semantics, such as order_id、payment_id
• Status field:status or biz_status
• Original external status:raw_status or channel_status
• Index name:uk_ indicates a unique index,idx_ indicates a regular index

---

7) Notes

1. Status fields use numeric status codes by default, with semantics maintained by enums at the application layer; string statuses are only suitable for storing external raw statuses or a small number of low-frequency scenarios
2. Business unique identifiers must be designed together with unique constraints, and the database should preferably bear the fallback responsibility for idempotency
3. deleted must be NOT NULL DEFAULT 0, and do not omit the soft-delete semantic comment
4. Except for setting-type time information, business time fields should uniformly use timestamp(6); do not mistake microsecond precision for a concurrency control solution
5. Index design must be based on real SQL and execution plans; do not treat the order of any template as a universal standard answer
6. The partition key must be included in the primary key and unique indexes (MySQL partitioned table limitation)