Database Standards That Keep Large Chinese Tech Teams From Breaking Production

Foreword

From table creation to SQL optimization, helping you avoid three years of detours

A few days ago, I published an article titled "Git Standards at Top-tier Internet Companies," which was quite popular across the internet.

Today, while the iron is hot, let's discuss the database standards of top-tier internet companies. I hope it will be helpful to you.

In some large companies, with R&D teams of hundreds or even thousands of people, databases remain in perfect order.

Table structures are clear, naming conventions are unified, index designs are reasonable, and SQL performance is controllable.

Much of this experience is worth learning from.

More project practices at Java Assault Team website: susan.net.cn/project

1. Why Do Large Companies Place So Much Importance on Database Standards?

Before discussing specific standards, let's first understand a fundamental question—why do large companies consider database standards so important?

The first reason: The database is the "least changeable" layer.

Code can be refactored, and architecture can evolve, but once a database table goes live, field names and field types are basically unchangeable.

Changing a field name requires changing all the business code that depends on it, and it cannot be pre-released for testing. Every line of DDL deserves your careful attention.

The second reason: Data volume grows "exponentially."

A table created with a "just get it online first" mentality might grow to millions of rows in just three months. By the time problems are discovered and optimized, the cost is ten or even a hundred times higher than following the standard from the start.

The third reason: The database is the "lifeblood" of the entire system.

If code has problems, at most a feature becomes unavailable.

If the database has problems, the entire system is paralyzed. The Alibaba standards heavily use "mandatory" clauses precisely because Alibaba has experienced countless extreme tests during Singles' Day and deeply understands what database problems mean.

Frankly, database standards are not for "managing people," but for "saving lives."

2. Table Creation Conventions

Life or death is decided from the first line of DDL.

2.1 Naming Conventions

This is the part most easily overlooked and hardest to change.

① Table names and field names must be all lowercase; uppercase is prohibited

The Alibaba standard mandates: Table names and field names must use lowercase letters or numbers; starting with a number is prohibited.

-- ✅ Correct
CREATE TABLE user_info (...);
CREATE TABLE order_detail (...);

-- ❌ Incorrect—contains uppercase letters
CREATE TABLE UserInfo (...);
CREATE TABLE OrderDetail (...);

Why all lowercase?

MySQL is case-insensitive on Windows but case-sensitive by default on Linux.

Once deployed to a Linux environment, System and system become two different tables.

Using all lowercase completely avoids this pitfall.

② Table names should be singular; plural is prohibited

Table names represent the entity content, not the quantity of entities.

-- ✅ Correct
CREATE TABLE user (...);
CREATE TABLE order (...);

-- ❌ Incorrect—used plural
CREATE TABLE users (...);
CREATE TABLE orders (...);

③ Reserved words are prohibited

MySQL reserved words like desc, range, match, delayed cannot be used as table names or field names.

④ Unified index naming

Index Type	Naming Format	Example
Primary Key Index	pk_field_name	pk_id
Unique Index	uk_field_name	uk_user_name
Normal Index	idx_field_name	idx_create_time

Knowing the index type just by looking at the name saves half the effort during troubleshooting.

⑤ Table name length should not exceed 32 characters

Database names, table names, and field names should ideally not exceed 32 characters; just enough to "know the meaning by name."

2.2 Field Specifications—Choosing the Right Type Gets Twice the Result with Half the Effort

① Boolean fields: is_xxx + unsigned tinyint

This is one of the most classic clauses in the Alibaba standard:

-- ✅ Correct
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is deleted: 0-not deleted, 1-deleted'
is_valid TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Is valid: 0-invalid, 1-valid'

-- ❌ Incorrect—non-standard field name
deleted TINYINT COMMENT 'Is deleted'

1 means yes, 0 means no. Any field that is non-negative must use unsigned.

Special Note: Although the database field must be named is_xxx, the corresponding boolean variable in the Java POJO class cannot have the is prefix (e.g., isDeleted cannot be used), and mapping must be done in the resultMap. Otherwise, it may cause serialization failures or RPC framework value retrieval exceptions.

② Decimal types: Always use decimal; float and double are prohibited

This is an "iron rule" for money-related businesses.

-- ✅ Correct
price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Price'

-- ❌ Incorrect—floating-point numbers have precision loss
price FLOAT NOT NULL COMMENT 'Price'

float and double are binary approximate storage; reconciliation often reveals discrepancies of a few cents. Once amounts or exchange rates are stored using float, online reconciliation discrepancies are only a matter of time.

③ String types: char vs varchar vs text

• Almost equal length → Use char (fixed length)
• Uncertain length → Use varchar, but do not exceed 5000
• Text exceeding 5000 → Use text, store in a separate table to avoid affecting the indexing efficiency of other fields in the main table

-- ✅ Correct—short text
user_name VARCHAR(32) NOT NULL COMMENT 'Username'

-- ✅ Correct—extra-long text stored independently
-- Main table
CREATE TABLE article (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL COMMENT 'Title'
);

-- Content independent table
CREATE TABLE article_content (
    article_id BIGINT UNSIGNED PRIMARY KEY COMMENT 'Article ID',
    content TEXT NOT NULL COMMENT 'Article content'
);

④ Three mandatory fields for every table: id, create_time, update_time

The Alibaba standard mandates these three fields for every table:

-- ✅ Correct
CREATE TABLE user (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary Key ID',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
    PRIMARY KEY (id)
);

• id: Primary key, bigint unsigned, auto-increment with step 1 for single tables
• create_time: datetime type, indicating creation time
• update_time: datetime type, indicating update time—without update_time, when a data problem occurs, you can't even find out "when it was changed"

3. Index Specifications

Used well, indexes are "accelerators"; used poorly, they are "dead weight."

Indexes are the core of MySQL performance optimization, but more indexes are not always better.

3.1 Five Principles of Index Design

① Fields with unique business characteristics must have a unique index

Even if the business layer performs uniqueness checks, a unique index must be established at the database layer.

-- ✅ Correct
CREATE UNIQUE INDEX uk_user_name ON user(user_name);

The Alibaba standard emphasizes: Application-layer uniqueness checks are insufficient. Only a unique index at the database layer can completely prevent duplicate data in concurrent scenarios.

② No more than 5 indexes per table

More indexes are not better. Each index affects write performance. When table data is updated, all indexes must be updated synchronously. It is recommended to keep the number of indexes per table within 5.

③ Composite indexes follow the leftmost prefix principle

When creating a composite index, place fields with high selectivity first. Query conditions must start from the leftmost column of the index to hit the index.

-- Composite index (user_id, create_time)
-- ✅ Can hit the index
WHERE user_id = 1 AND create_time > '2024-01-01'
WHERE user_id = 1

-- ❌ Cannot hit the index
WHERE create_time > '2024-01-01'

④ Prohibit creating indexes on columns with frequent updates and low cardinality

For low-cardinality columns like status or type, MySQL's optimizer will likely not use the index anyway. It not only wastes storage space but also slows down write performance.

⑤ Prohibit mathematical operations and function operations on indexed columns

Once an operation is performed on an indexed column, the index becomes invalid immediately.

-- ❌ Incorrect—index column participates in operation, index invalidated
SELECT * FROM user WHERE YEAR(create_time) = 2024;

-- ✅ Correct—perform operation on the other side of the equals sign
SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

3.2 Three Typical Index Mistakes

Error Scenario	Consequence	Correct Approach
Inconsistent field types	Index invalidated, full table scan	JOIN field types must be absolutely consistent
Function operation on indexed column	Index invalidated	Operate on the value, not the column
Implicit type conversion	Index invalidated	Keep field type consistent with query value type

A real case: A table with 20 million rows, WHERE status = ? took 8 seconds. The reason might be hard to believe—the status field was varchar, but the code passed an int. MySQL performed an implicit conversion, and the index was directly invalidated. This kind of pitfall is something almost every clause in the MySQL development standards reminds you about.

4. SQL Writing Standards

Let's write SQL that "speaks for itself."

4.1 Query Standards

① SELECT * is prohibited

Explicitly specify the fields needed.

SELECT * returns all columns, wasting network bandwidth and memory, and cannot utilize covering index optimization.

-- ❌ Incorrect
SELECT * FROM user WHERE id = 1;

-- ✅ Correct
SELECT id, user_name, email FROM user WHERE id = 1;

② JOINs exceeding three tables are prohibited

The Alibaba standard explicitly stipulates: JOINs exceeding three tables are prohibited.

JOINs consume significant memory and create temporary tables.

-- ❌ Incorrect—JOIN exceeding 3 tables
SELECT * FROM a JOIN b ON a.id = b.a_id 
JOIN c ON b.id = c.b_id 
JOIN d ON c.id = d.c_id;

-- ✅ Correct—split into multiple queries, assemble at the application layer
SELECT * FROM a WHERE ...
SELECT * FROM b WHERE a_id IN (...)
SELECT * FROM c WHERE b_id IN (...)

③ JOIN fields must have indexes

The associated fields must have indexes.

Moreover, the data types of JOIN fields must be absolutely consistent. Inconsistent types will cause index invalidation.

④ Avoid performing calculations in the database

MySQL is not good at mathematical operations and logical judgments. Any calculation that can be placed in the application layer should definitely not be in SQL.

4.2 Key Points for Data Type Selection

Data Type	Standard Requirement
Integer	Use unsigned if no negative numbers, can expand the representation range
Decimal	Always use decimal; float/double prohibited
Time	Use datetime or timestamp
Monetary Amount	decimal type, no precision loss
Character Set	Uniformly use utf8mb4

4.3 Three "Avoids"

Principle	Reason
Avoid count(*)	Poor performance with large data volumes
Avoid using NULL fields	Indexes may become invalid; statistics may be abnormal
Avoid large SQL, large transactions, large batches	Easy to drag down the database

5. ORM Mapping Standards

This is the "last mile" for the Java layer.

5.1 Field Mapping Rules

Database boolean fields are named is_xxx, but boolean variables in POJO classes cannot have the is prefix.

// ❌ Incorrect—boolean variable has is prefix
@Data
public class UserDO {
    private Boolean isDeleted;  // Serialization may fail
}

// ✅ Correct—no is prefix
@Data
public class UserDO {
    private Boolean deleted;    // Map is_deleted → deleted in resultMap
}

<!-- resultMap mapping -->
<resultMap id="userMap" type="UserDO">
    <result column="is_deleted" property="deleted"/>
</resultMap>

5.2 Logical Deletion vs. Physical Deletion

Large companies generally recommend logical deletion over physical deletion.

Dimension	Physical Deletion	Logical Deletion
Data Traceability	❌ Irrecoverable	✅ Operation records traceable
Uniqueness Constraint	No conflict	Need to handle unique key reuse
Storage Footprint	Saves space	One extra row for the marker
Query Complexity	Simple	Every WHERE clause needs is_deleted
Applicable Scenarios	Temporary tables, rebuildable data	Core business data, requiring audit

The benefit of logical deletion is data traceability; the downside is that originally unique keys may no longer be unique. This needs to be handled separately based on the business scenario.

6. A Panoramic View of Large Company Database Standards

7. Data Volume Threshold Reference

Threshold	Standard Requirement
Single table rows > 5 million	Recommend sharding
Single table capacity > 2GB	Recommend sharding
Not expected to reach within 3 years	Do not recommend premature sharding

8. Pros and Cons

Pros

1. Significantly improved code maintainability

Unified naming conventions allow team members to understand table structures and field meanings without additional communication. A newcomer can understand the purpose of a table just by looking at its name.

2. Significantly reduced performance issues

Index standards and SQL standards eliminate the hidden dangers of slow queries from the source. The Alibaba standards heavily use "mandatory" clauses precisely because Alibaba has experienced countless extreme tests during Singles' Day.

3. Data security is guaranteed

Logical deletion ensures data traceability, unique indexes ensure no duplicate data, and decimal ensures no precision loss for monetary amounts.

4. High team collaboration efficiency

Unified standards provide a basis for Code Review, rules for DBAs to follow, and regulations for developers to adhere to.

5. Problem investigation has clues to follow

Standardized field comments, unified index naming, and mandatory time fields provide clues for investigating online problems.

Cons

1. Standards require tool support

Without tools to enforce implementation, standards are just a piece of waste paper. They need to be paired with SQL audit tools, CI gates, and other mandatory checks.

2. Initial adaptation cost

When a team switches from "free mode" to "standard mode," there will be some discomfort in the first few weeks.

3. Need to combine with actual scenarios

Standards are "general guides," not "iron laws." Some extreme scenarios may require appropriate adjustments, but this must be done on the premise of fully understanding the principles behind the standards.

4. Style differences between Alibaba and ByteDance

Dimension	Alibaba	ByteDance
Design Philosophy	Conservative, emphasizes stability	Flexible, emphasizes development efficiency
Constraint Strength	Many "mandatory" clauses	Higher proportion of "recommended" suggestions
Name Length	Strictly enforces 32-character limit	Suggests "as short as possible" but no hard constraint
Primary Key Type	Mandates bigint unsigned	Recommends choosing based on actual range

There is no absolute right or wrong between the two; choose the standard that suits your team size and business characteristics.

More project practices at Java Assault Team website: susan.net.cn/project

9. Final Words

Returning to the initial question: Why do large companies place so much importance on database standards?

Because the database is the most "unchangeable" layer in the entire system.

Code can be refactored, and architecture can evolve, but once a database table goes live, field names and field types are basically unchangeable.

Every line of DDL deserves your careful attention.

The Alibaba standards heavily use "mandatory" clauses because Alibaba has experienced countless extreme tests during Singles' Day and deeply understands what database problems mean.

ByteDance's standards are more flexible because a rapidly iterating product culture requires more autonomous decision-making space.

Regardless of the style, the core goal is the same: to make the database withstand the test of time.

If you are just starting to pay attention to database standards now, it is recommended to start with three things:

Step 1: Unify naming conventions. Table names and field names should be all lowercase with underscores, boolean fields should use is_xxx, and decimals should use decimal. Once a field name goes live and is referenced by the business, changing it affects the whole system.

Step 2: Mandate id, create_time, update_time for every table. Without update_time, when a problem occurs, you can't even find out "when it was changed."

Step 3: Use tools to enforce implementation. SQL audit tools, CI gates, Code Review—ensure every SQL statement is checked before going live.

The most valuable piece of experience: Once a table goes live, field names and types are basically unchangeable. So every line of DDL deserves your careful attention.