Comprehensive Database Design, Security, and Operational Best Practices

Security Standards – Enforce encryption of passwords, prohibit storing plaintext sensitive data, require approval for data export, mandate audit logs, IP whitelisting, monitoring of critical SQL, regular credential rotation, and other security controls.

Basic Standards – Avoid heavy computations in the database, reject large SQL statements and transactions, minimize use of stored procedures, add Chinese comments to tables/fields, store large objects outside the DB, follow the principle of least privilege, and consider scalability during design.

Naming Standards – Use lowercase, underscore‑separated names not exceeding 32 characters, avoid reserved words, prefix temporary tables with tmp_ , backup tables with bak_ , and follow consistent naming conventions for indexes and boolean fields.

Table Design Standards – Prefer InnoDB, use UTF8/UTF8MB4 charset, isolate business domains into separate databases, and deploy high‑availability architectures such as MHA for all production databases.

Field Design Standards – Define fields as NOT NULL with defaults, replace ENUM with TINYINT, avoid TEXT/BLOB for large tables, store phone numbers as VARCHAR(20), store monetary values as integer cents, use DECIMAL for precise numbers, and apply UNSIGNED where appropriate.

Index Design Standards – Limit indexes per table, avoid indexing low‑selectivity columns, place high‑cardinality columns first in composite indexes, use prefix indexes for long strings, enforce non‑NULL index columns, and prefer unique indexes when possible.

SQL Usage Standards – Prohibit SELECT * , require explicit column lists, avoid INSERT without column specification, prevent implicit type conversion, disallow functions in WHERE clauses, limit use of OR, batch INSERTs, and rewrite large LIMIT offsets for better performance.

Behavioral Standards – Restrict direct access to production databases, require DBA approval for write operations, forbid online stress testing, and separate development/test environments from production.

Process Standards – Require advance notification of table creation, index planning, and schema changes; enforce review periods; mandate audit logging; and ensure rollback plans for any production changes.

Example Table Definition

CREATE TABLE `student_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `stu_name` varchar(10) NOT NULL DEFAULT '' COMMENT '姓名',
  `stu_score` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '总分',
  `stu_num` int(11) NOT NULL COMMENT '学号',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `status` tinyint(4) DEFAULT '1' COMMENT '1代表记录有效，0代表记录无效',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_student_info_stu_num` (`stu_num`) USING BTREE,
  KEY `idx_student_info_stu_name` (`stu_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表';