Database Standards and Guidelines for Backend Development

The author, responsible for backend database standards, drafted a unified specification that has been refined over several versions and adopted across all product lines.

After more than six months of enforcement, the guidelines have helped the team avoid inappropriate CREATE TABLE statements, erroneous SQL, and incorrect indexes during development.

The specification is divided into three sections—Table Creation Rules, SQL Rules, and Index Rules—each containing mandatory (strong) and suggested (optional) items that teams can adapt to their own contexts.

Table Creation Rules

Mandatory: Use InnoDB as the storage engine. Explanation: InnoDB provides transactions, row‑level locking, and better concurrency.

Mandatory: Every table must have a primary key ID using an auto‑increment column (keep it short when possible). Explanation: A monotonic primary key improves insert performance and reduces page splits.

Mandatory: Use the utf8mb4 character set. Explanation: utf8mb4 is the true UTF‑8 implementation in MySQL.

Mandatory: Add Chinese comments to tables and columns. Explanation: Improves readability and maintenance.

Mandatory: Names (database, tables, columns) must be lowercase, use snake_case, be ≤32 characters, and be meaningful without mixing pinyin and English.

Mandatory: Limit the number of columns per table to less than 30; otherwise consider splitting the table.

Mandatory: Prohibit foreign keys; enforce referential integrity in the application layer.

Mandatory: Define columns as NOT NULL with default values. Explanation: NULL columns increase index complexity, storage overhead, and reduce optimizer efficiency.

Mandatory: Disallow reserved words (e.g., DESC, RANGE, MARCH) as identifiers.

Mandatory: Use CHAR for fixed‑length strings when lengths are uniform.

Recommended: Prefer TIMESTAMP over DATETIME when appropriate.

Recommended: Manually write all schema definitions rather than relying on auto‑generated scripts.

SQL Rules

Recommended: Avoid custom functions, stored functions, and user variables to enable query caching.

Mandatory: Explicitly list required columns in SELECT statements instead of using *.

Mandatory: Disallow implicit type conversion in queries.

Mandatory: Do not use functions or expressions on indexed columns in WHERE clauses, as they prevent index usage.

Mandatory: Prohibit the use of % at the beginning of LIKE patterns; use external search engines if needed.

Index Rules

Recommended: Avoid creating separate indexes on low‑cardinality or frequently updated columns.

Mandatory: Limit JOINs to at most five tables; ensure join columns have identical data types and are indexed.

Mandatory: If the first column of a composite index has cardinality 1, do not create the composite index.

Mandatory: Place higher‑cardinality columns on the left side of composite indexes.

Recommended: Leverage covering indexes to avoid table lookups.

Recommended: Specify index length for long VARCHAR columns based on actual selectivity.

Recommended: Align ORDER BY clauses with index order to utilize index sorting.

Recommended: Do not use indexed columns as part of expressions or function arguments in WHERE clauses.

Recommended: Only the leftmost range condition in a multi‑range query can use an index.

Recommended: Ensure join columns across tables have exactly the same type, length, charset, and collation.

References: High Performance MySQL (3rd Edition) and Alibaba Java Development Manual .