Database Object Naming and Design Standards

Database Object Naming Conventions

Database objects such as tables, indexes, views, defaults, rules, triggers, stored procedures, and users require consistent naming rules. Names should use meaningful English words separated by underscores, start with a letter, contain only letters, numbers, and underscores, avoid MySQL reserved words, and be all lowercase.

Global Naming Rules

Use meaningful English words with underscores.

Names must start with a letter and contain only letters, numbers, and underscores.

Avoid MySQL reserved words (e.g., backup, call, group).

All object names are lowercase.

Database Naming

Limit to 30 characters.

Use project name plus a short meaning (e.g., im_flow ).

Specify default character set (UTF8/utf8mb4) and collation on creation.

Names are lowercase.

Table Naming

Regular tables: prefix t_ followed by module abbreviation and table meaning (e.g., t_user_eduinfo ).

Temporary tables: temp_ + module + table + date (e.g., temp_user_eduinfo_20210719 ).

Backup tables: bak_ + module + table + date.

Use consistent prefixes within a module and keep names meaningful and under 30 characters.

Column Naming

Use English words or abbreviations, separated by underscores (e.g., service_ip ).

Same meaning columns across tables must share the same name (e.g., create_time ).

All lowercase, under 30 characters.

Index Naming

Unique index: uni_<column_name> (e.g., uni_uid ).

Non‑unique index: idx_<column_names> (e.g., idx_uname_mobile ).

Use underscores, lowercase, and keep names under 50 characters.

Follow left‑most prefix principle and avoid redundant indexes.

View Naming

Prefix with v (e.g., v_user or v_user_order for multi‑table views).

Keep under 30 characters, lowercase, and avoid creating views unless necessary.

Stored Procedure Naming

Prefix with sp_ , use underscores, reflect functionality, and keep under 30 characters. Input parameters start with i_ , output parameters with o_ .

create procedure sp_multi_param(in i_id bigint, in i_name varchar(32), out o_memo varchar(100))

Function Naming

Prefix with func_ , use underscores, reflect functionality, and keep under 30 characters.

create function func_format_date(ctime datetime)

Trigger Naming

Prefix with trig (e.g., trig_attach_log_d ).

Suffix _i , _u , or _d indicates insert, update, or delete.

DROP TRIGGER IF EXISTS trig_attach_log_d;
CREATE TRIGGER trig_attach_log_d AFTER DELETE ON t_dept FOR EACH ROW;

Constraint Naming

Unique constraint: uk_<table>_<column> .

Foreign key: fk_<child_table>_<parent_table> .

Prefer NOT NULL and default values; avoid foreign keys when possible.

User Naming

Production users: code_<app> .

Read‑only users: read_<app> .

Database Object Design Guidelines

Use InnoDB as the default storage engine.

Prefer UTF8 or UTF8MB4 character sets.

Design tables to be independent; avoid foreign keys and excessive columns (max ~50).

Ensure each table has a primary key, follow third normal form, and limit table size to ~16 GB and rows to ~20 million.

Use appropriate data types: UNSIGNED INT for IDs, DATETIME (avoid TIMESTAMP unless needed), VARCHAR with sensible length, DECIMAL for precise numbers, and limit TEXT/BLOB usage.

Auto‑increment fields must be UNSIGNED INT or BIGINT and part of the primary key.

Index Design Guidelines

Create indexes on high‑selectivity columns (selectivity > 0.2).

Follow the left‑most prefix rule for composite indexes.

Limit indexes per table to about five; prefer covering indexes for ORDER BY, GROUP BY, DISTINCT.

Use prefix indexes for TEXT columns, avoid functions on indexed columns, and avoid leading‑wildcard LIKE patterns.

Constraint Design Guidelines

Primary keys should be short, ordered, auto‑incrementing, and immutable.

Unique constraints use uk_ prefix.

Avoid foreign key constraints; enforce referential integrity in application code.

All non‑PK columns should be NOT NULL with default values unless a special case applies.

SQL Usage Guidelines

Avoid SELECT * ; specify needed columns to enable index‑only scans.

Never use SELECT * without a WHERE clause.

Separate TEXT fields from other columns; avoid selecting them unless necessary.

Do not use nondeterministic functions (NOW(), RAND(), etc.) in WHERE clauses.

Always include ORDER BY in pagination queries.

Prefer IN/UNION over OR, and keep IN list size < 300.

Use trailing‑wildcard LIKE (e.g., name% ) instead of leading‑wildcard.

Replace subqueries with joins when possible.

For INSERT statements, always list column names.

Batch large UPDATE/DELETE/INSERT operations to reduce master‑slave lag.

Programmatic Constraints

The team plans to build a review tool that automatically checks submitted DDL/DML statements against the above standards and rejects non‑compliant submissions.