21 Essential MySQL Table Design Rules Every Backend Engineer Should Follow

1. Naming Conventions

Use clear, readable English names for tables, columns, and indexes. Avoid numeric prefixes, pinyin, and obscure abbreviations. Examples of bad column names: acc_no, 1_acc_no, zhanghao. Good examples: account_no, account_number. Index naming guidelines: pk_column for primary keys, uk_column for unique indexes, and idx_column for regular indexes.

2. Choose Appropriate Field Types

Select the smallest suitable numeric type, e.g., tinyint, smallint, int, bigint.

For monetary values, use decimal and avoid float or double.

If string lengths are uniform, prefer fixed‑length char.

Use varchar for variable‑length strings, keeping the length ≤ 5000.

For very large text, use text and store the content in a separate table linked by a primary key.

The total length of all varchar columns in a table must not exceed 65535; otherwise use TEXT/LONGTEXT.

3. Reasonable Primary Key Design

Avoid business‑related keys such as ID numbers. Prefer meaningless, unique identifiers like UUID, auto‑increment integers, or Snowflake IDs.

4. Choose Proper Field Length

MySQL varchar and char store character length, while other types store byte length. For example, char(10) is 10 characters, but bigint(4) actually occupies 8 bytes.

Set realistic lengths, e.g., username varchar(32) for a username of 5–20 characters, preferring powers of two.

5. Prefer Logical Deletion Over Physical Deletion

Physical delete removes rows permanently: delete from account_info_tab where account_no='666'; Logical delete marks rows with a flag, e.g., is_deleted = 1 via an update statement.

Logical deletion preserves data for recovery, keeps primary key sequences continuous, and is safer for core business tables.

6. Common Columns for Every Table

id

– primary key create_time – record creation timestamp modified_time / update_time – last update timestamp version – optimistic lock version (optional) remark – notes (optional) modified_by – updater (optional) creator – creator (optional)

7. Limit Number of Columns

Keep column count ≤ 20. If many attributes are needed, split the data into multiple related tables sharing the same primary key.

8. Use NOT NULL Whenever Possible

Prevents null‑pointer issues.

Reduces storage overhead and simplifies index usage.

Ensures consistent data handling.

9. Index Design Guidelines

Only add indexes when table size justifies them; avoid indexes on tables with a few dozen rows.

Limit total indexes per table to around 5 to protect write performance.

Do not index low‑cardinality columns (e.g., gender).

Avoid functions on indexed columns, as they can invalidate the index.

Use composite indexes following the left‑most prefix rule.

Example table creation with indexes:

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  UNIQUE KEY `un_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

10. 3NF Is Not Mandatory

While third normal form eliminates redundancy, occasional denormalization (e.g., storing total_amount alongside price * quantity) can improve query performance by trading space for speed.

11. Avoid MySQL Reserved Words

When a name collides with a reserved word (e.g., select, desc), you must quote it with backticks, complicating SQL and scripts. Prefer non‑reserved identifiers.

12. Do Not Use Foreign Keys

Foreign keys can cause performance bottlenecks, deadlocks, and difficulties in sharding. Alibaba’s Java guidelines even forbid foreign keys and cascade operations; handle relationships in application code instead.

13. Prefer InnoDB Storage Engine

Use INNODB unless the read/write ratio is below 1%, in which case MyISAM might be considered. Other engines like MEMORY should be used only under DBA guidance.

14. Consistent Character Set

Standardize on utf8 (or utf8mb4 for full Unicode, including emojis). Other MySQL charsets include GBK and latin1.

15. Document Enum Values in Comments

For enum‑type columns, add clear explanations in the COMMENT clause so future maintainers understand each code.

`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT '00: online, 01: offline, 02: forced logout, 03: logged in elsewhere'

16. Choose Proper Time Types

date

– YYYY‑MM‑DD, 3 bytes. time – HH:MM:SS, 3 bytes. datetime – YYYY‑MM‑DD HH:MM:SS, 8 bytes, timezone‑independent (recommended). timestamp – Unix‑style, 4 bytes, timezone‑aware. year – 1 byte, range 1901‑2155.

17. Avoid Stored Procedures and Triggers

MySQL’s stored procedures and triggers lack robust error handling and can complicate maintenance; use them sparingly.

18. 1:N Relationship Modeling

Place a foreign‑key‑like column (e.g., class_id) in the “many” side table to reference the “one” side primary key. For many‑to‑many relationships, introduce a junction table.

19. Handling Large Fields

Store massive text or binary data in a separate system such as MongoDB and keep only a reference ID in the relational table, similar to storing image URLs instead of binary blobs.

20. Consider Sharding (Database/Table Partitioning)

When tables approach millions of rows, evaluate horizontal (multiple databases) or vertical (splitting tables) sharding to maintain query performance. Common sharding strategies include range and hash partitioning, but they introduce challenges like distributed transactions and cross‑shard joins.

21. SQL Writing Tips

Avoid SELECT *; specify needed columns.

Use LIMIT 1 when only one row is required.

Prefer AND over OR in WHERE clauses.

Beware of deep pagination with large LIMIT offsets.

Filter rows early in WHERE to reduce result sets.

Do not wrap indexed columns in functions.

Avoid expressions on indexed columns in WHERE.

Prefer = over != or <> for better index usage.

Follow the left‑most rule for composite indexes.

Batch inserts for high‑volume loads.

Use covering indexes when possible.

Analyze execution plans with EXPLAIN.