Comprehensive Guide to Database Object Naming and Design Standards

Database Object Naming Conventions

Database objects include tables, indexes, views, diagrams, defaults, rules, triggers, stored procedures, users, etc. Naming rules cover schemas, tables, indexes, constraints, and more.

Global Naming Rules

Use meaningful English words separated by underscores.

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

Avoid MySQL reserved words such as backup, call, group.

All object names should be lowercase for uniformity.

Database Naming

Limit to 30 characters.

Typically use projectName_shortForm, e.g., im_flow.

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

Use lowercase.

Table Naming

Regular tables start with t_ followed by module abbreviation and table purpose, e.g., t_user_eduinfo.

Temporary tables: temp_ + module + table + date suffix.

Backup tables: bak_ + module + table + date suffix.

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

Separate words with underscores and use lowercase.

Column Naming

Use clear English words or abbreviations, separated by underscores, e.g., service_ip, service_port.

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

Limit to 30 characters and use lowercase.

Index Naming

Unique index: uni_ + column name, e.g., uni_uid.

Non‑unique index: idx_ + column names, e.g., idx_uname_mobile.

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

Follow left‑most prefix principle and avoid excessive columns.

View Naming

Prefix with v, e.g., v_user_info.

If based on a single table, use v_ + table name; for joins, concatenate table names with underscores.

Limit to 30 characters, use lowercase, and generally prohibit developers from creating views unless required.

Stored Procedure Naming

Prefix with sp_, use underscores between words, reflect functionality, limit to 30 characters.

Input parameters start with i_, output parameters with o_.

All lowercase.

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 purpose, limit to 30 characters.

All lowercase.

create function func_format_date(ctime datetime)

Trigger Naming

Prefix with trig, include target table name, keep under 30 characters.

Suffix _i, _u, _d for insert, update, delete.

All lowercase.

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, e.g., uk_user_name.

Foreign key: fk_ + childTable + parentTable, e.g., fk_user_dept.

Prefer NOT NULL and default values; avoid foreign keys in favor of application‑level integrity.

User Naming

Production users: code_app.

Read‑only users: read_app.

Database Object Design Standards

Storage Engine Selection

Use InnoDB by default unless special requirements dictate otherwise. InnoDB supports transactions and foreign keys, whereas MyISAM is faster but lacks these features.

Charset Selection

Prefer utf8 or utf8mb4. utf8mb4 is a superset of utf8 and supports four‑byte Unicode characters.

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

Table Design Guidelines

Minimize cross‑application table relationships; avoid foreign keys, enforce integrity in code.

Design tables per component rather than for the whole system.

Every table should have a primary key (PK), preferably an auto‑increment unsigned INT or BIGINT.

Each column should represent a single meaning; avoid duplicate columns.

Do not use complex data types; JSON usage is optional.

Join keys must have identical data types.

Follow at least Third Normal Form; denormalize only with justification.

Store large TEXT fields in separate tables; avoid TEXT/BLOB unless necessary.

Limit table size to ~16 GB and rows to ~20 million (performance degrades beyond this).

Consider partitioning or sharding for very large tables; avoid partition tables unless required.

Field Type Guidelines

Use UNSIGNED INT for integer IDs; length indicates display width.

Prefer DATETIME over TIMESTAMP unless timezone conversion is needed.

Use VARCHAR(N) with N as small as possible; total row length limited to 65 535 bytes.

Use DECIMAL for precise numeric values; avoid FLOAT / DOUBLE.

Avoid BLOB and large TEXT types unless absolutely necessary.

Prefer NOT NULL with sensible defaults.

Auto‑increment columns must be unsigned and part of the PK.

Index Design Guidelines

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

Follow left‑most prefix rule for composite indexes; place most selective columns first.

Use prefix indexes for TEXT columns.

Limit to about five indexes per table; prioritize read‑heavy workloads.

Include ORDER BY/GROUP BY/DISTINCT columns in covering indexes when possible.

Avoid functions or operations on indexed columns in WHERE clauses.

Prefer value% over %value for LIKE patterns.

Constraint Design Guidelines

PK should be ordered, meaningless, short, and auto‑increment.

Unique constraints use uk_ prefix.

PK fields are immutable.

Do not create foreign key constraints; enforce referential integrity in application code.

All fields should be NOT NULL and have default values unless a special case applies.

SQL Usage Standards

Select Statements

Avoid SELECT *; specify needed columns to reduce I/O and enable index‑only scans.

Never use SELECT * FROM table without a WHERE clause.

Avoid functions like NOW(), RAND() in SELECT lists when deterministic results are required.

Always include ORDER BY in pagination queries.

Prefer IN() over multiple OR conditions; keep IN list < 300 items.

Do not use leading wildcard in LIKE (e.g., %value); use trailing wildcard instead.

Replace simple subqueries with JOINs when possible.

DML Statements

Never use INSERT without column list; always specify columns.

Batch large UPDATE/DELETE/INSERT operations to avoid replication 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 code.