Master Database Naming: Standards, Design & SQL Best Practices

Database Object Naming Conventions

Database Objects

Common database objects include tables, indexes, views, diagrams, defaults, rules, triggers, stored procedures, users, etc. Naming conventions apply to schemas, tables, indexes, constraints, and more.

Global Naming Rules

1. Use meaningful English words separated by underscores. 2. Names may contain only letters, numbers, and underscores, and must start with a letter. 3. Avoid MySQL reserved words (e.g., backup, call, group). 4. Use lowercase for all objects.

Database Naming

1. Keep names under 30 characters. 2. Use project name plus a short meaning, e.g., im_flow . 3. Always specify default character set (UTF8) and collation. 4. Use lowercase.

Table Naming

1. Regular tables start with t_ followed by module and table abbreviations, e.g., t_user_eduinfo . 2. Temporary tables use temp prefix with module, table, and date, e.g., temp_user_eduinfo_20210719 . 3. Backup tables use bak prefix, e.g., bak_user_eduinfo_20210719 . 4. Keep prefixes consistent within a module. 5. Separate words with underscores. 6. Keep names under 30 characters and lowercase.

Column Naming

1. Use meaningful English words or abbreviations, separated by underscores, e.g., service_ip , service_port . 2. Identical meaning columns across tables must share the same name, e.g., create_time . 3. Separate words with underscores. 4. Keep names under 30 characters and lowercase.

Index Naming

1. Unique indexes: prefix uni_ + column name, e.g., create unique index uni_uid on t_user_basic(uid) . 2. Non‑unique indexes: prefix idx_ + column name, e.g., create index idx_uname_mobile on t_user_basic(uname,mobile) . 3. Separate words with underscores. 4. Keep index names under 50 characters and lowercase. 5. Use concise abbreviations for multi‑column indexes, e.g., idx_mid_fid for member_id and friend_id . 6. Follow the left‑most prefix rule for composite indexes.

View Naming

1. Prefix with v followed by a short meaning, e.g., v_user_summary . 2. If based on a single table, use v_ + table name; for multiple tables, concatenate table names with underscores. 3. Keep names under 30 characters and lowercase. 4. Creation of views should be avoided unless necessary.

Stored Procedure Naming

1. Prefix with sp , use underscores between words, reflect functionality, and keep under 30 characters. 2. Input parameters start with i_ , output parameters with o_ . 3. Use lowercase.

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

Function Naming

1. Prefix with func , use underscores, reflect functionality, and keep under 30 characters. 2. Use lowercase.

create function func_format_date(ctime datetime)

Trigger Naming

1. Prefix with trig . 2. Include the target table name and a suffix indicating the operation ( _i , _u , _d ). 3. Keep names under 30 characters and 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

1. Unique constraints: uk_ + table + column, e.g., ALTER TABLE t_dept ADD CONSTRAINT un_name UNIQUE (name) . 2. Foreign keys: fk_ + child table + parent table, e.g., ALTER TABLE t_user ADD CONSTRAINT fk_user_dept FOREIGN KEY (depno) REFERENCES t_dept (id) . 3. Prefer NOT NULL and default values; avoid foreign keys in favor of application‑level integrity.

User Naming

1. Production users: code_app . 2. Read‑only users: read_app .

Database Object Design Standards

Storage Engine Selection

Use InnoDB unless a special requirement dictates otherwise. Verify with show variables like 'default_storage_engine' . InnoDB supports transactions and foreign keys; MyISAM is faster but lacks these features.

Character Set Selection

Prefer utf8 or utf8mb4 . utf8mb4 is a superset of utf8 and handles four‑byte Unicode characters. Check charset with:

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

Table Design Guidelines

1. Minimize cross‑application table relationships; avoid foreign keys. 2. Design tables per component rather than for the whole system. 3. Every table should have a primary key. 4. Each column must represent a single meaning. 5. No duplicate columns. 6. Avoid complex data types; use JSON only when needed. 7. Ensure join columns have identical data types. 8. Aim for at least third normal form; denormalize only with justification. 9. Store large text in separate tables; avoid TEXT/BLOB unless necessary. 10. Archive or delete old data via partitioning or sharding (2/8 rule). 11. Limit columns to ≤50 per table. 12. Keep table size ≤16 GB and rows ≤20 million (adjust per workload). 13. Plan sharding early for large data volumes. 14. Do not use partitioned tables without a strong need.

Field Design Guidelines

1. Use UNSIGNED INT for integer fields; length indicates display width, e.g., `id` int(11) NOT NULL . 2. Use DATETIME for precise timestamps; avoid TIMESTAMP unless handling time zones. 3. Use VARCHAR for variable‑length strings, keeping length as small as possible (≤255 characters typical). 4. Use TEXT only when >20 000 characters are required, and store it in a separate table. 5. Use DECIMAL for exact numeric values; avoid FLOAT/DOUBLE. 6. Avoid BLOB unless absolutely necessary. 7. Prefer NOT NULL with sensible defaults. 8. Auto‑increment columns must be UNSIGNED INT or BIGINT and part of the primary key.

Index Design Guidelines

1. Create indexes on columns with high selectivity (selectivity > 0.2). 2. Follow the left‑most prefix rule for composite indexes. 3. Do not use foreign keys; enforce integrity in application code. 4. Use prefix indexes for TEXT columns. 5. Limit indexes per table to about five. 6. Place ORDER BY/GROUP BY/DISTINCT columns at the end of the index to form covering indexes. 7. Understand and calculate selectivity to avoid ineffective indexes. 8. Choose appropriate prefix lengths for prefix indexes. 9. Ensure left‑most matching for composite indexes; e.g., an index on (depno, empname, job) cannot use job if the query filters on empname first. 10. Retrieve only needed columns; use covering indexes when possible. 11. Consider index‑skip‑scan and index‑push‑down for further optimization. 12. Avoid functions or operations on indexed columns. 13. Avoid implicit type conversion in comparisons. 14. Use trailing‑wildcard LIKE (e.g., name% ) instead of leading‑wildcard. 15. Use index‑covered sorting fields. 16. Extend existing indexes rather than adding new ones when possible.

Constraint Design Guidelines

1. Primary keys should be ordered, meaningless, short, auto‑incrementing. 2. Unique constraints use uk_ prefix. 3. Primary key fields must not be updated. 4. Do not create foreign key constraints; enforce in code. 5. Apply NOT NULL to all fields unless a special case. 6. Provide default values for all fields unless a special case.

SQL Usage Standards

Select Query Best Practices

1. Avoid select * ; specify required columns to prevent unnecessary table scans. 2. Never use select * without a WHERE clause. 3. Store TEXT fields separately from other columns to improve I/O. 4. Avoid nondeterministic functions (now(), rand(), sysdate()) in SELECT lists and avoid any functions on WHERE columns. 5. Always include ORDER BY in pagination queries. 6. Prefer IN/UNION over OR; keep IN list < 300 items. 7. Do not use leading‑wildcard LIKE ( %value ); use trailing‑wildcard ( value% ) instead. 8. Replace subqueries with JOINs when possible; subqueries often prevent index usage and create temporary tables.

Operation Best Practices

1. Always specify column lists in INSERT statements. 2. Perform large‑scale UPDATE/DELETE/INSERT in batches to reduce master‑slave replication lag. 3. Be aware that row‑based binlog format generates more logs for bulk operations.