15 Essential Tips for Designing Robust Database Tables

Preface

For backend developers, accessing a database is an indispensable part of code. Core user data is usually stored in databases such as MySQL or Oracle for security. Daily work involves creating databases and tables to meet business needs, with table creation being the more frequent task. Ignoring table‑creation details can lead to high maintenance costs and pitfalls after deployment.

1. Naming

1.1 Meaningful Names

A good table, column, or index name should be concise, self‑descriptive, and pleasant to read, improving communication and maintenance. Bad names are ambiguous and chaotic.

用户名称字段定义成：yong_hu_ming、用户_name、name、user_name_123456789

Correct example:

用户名称字段定义成：user_name

Keep names under 30 characters.

1.2 Case

Prefer lowercase letters for readability. 字段名：PRODUCT_NAME、PRODUCT_name Correct example:

字段名：product_name

1.3 Separator

Use an underscore _ to separate words.

字段名：productname、productName、product name、product@name

Correct example:

字段名：product_name

1.4 Table Names

Include a business prefix, e.g., order_ for order‑related tables ( order_pay, order_pay_detail) or product_ for product tables ( product_spu, product_sku), to group related tables.

1.5 Column Names

Standardize column names to avoid confusion, e.g., use status instead of mixing flag and status. When a column stores a foreign key, append _id or _sys_no. Common columns: create_time, update_time, delete_status.

1.6 Index Names

Use prefixes: id or sys_no for primary keys, ix_ for ordinary or composite indexes (e.g., ix_product_status), and ux_ for unique indexes (e.g., ux_product_code).

2. Column Types

Select appropriate data types to balance storage and functionality. Choose the smallest type that satisfies business requirements: tinyint for small integers, int for 32‑bit numbers, bigint for large identifiers, decimal for precise monetary values, date / datetime / timestamp for temporal data, and varchar / char / text for strings.

Prefer the smallest storage that meets requirements.

Use char for fixed‑length strings; otherwise varchar.

Use bit for boolean fields.

Use tinyint for enumerations.

Use bigint for primary keys.

Use decimal for monetary fields.

Use timestamp or datetime for time fields.

3. Field Length

For varchar and char, the length denotes characters; for other types it denotes bytes. For example, bigint(4) still occupies 8 bytes, but displays up to 4 digits with zero‑fill if configured.

4. Number of Columns

Avoid tables with excessive columns; keep the column count under 20. Split large tables into smaller ones with the same primary key when necessary.

5. Primary Key

Always define a primary key; it provides the most efficient index. Use AUTO_INCREMENT for single‑node MySQL, but in distributed systems generate IDs externally (e.g., Snowflake) to ensure global uniqueness and avoid business coupling.

6. Storage Engine

MySQL 5.1+ defaults to InnoDB, which supports transactions and foreign keys. While MyISAM may be slightly faster for read‑heavy tables, InnoDB performance has improved, making it the recommended default.

7. NOT NULL

Define columns as NOT NULL whenever possible to save space and avoid index issues. Provide default values for new NOT NULL columns to prevent insert failures during schema migrations.

alter table product_sku add column brand_id int(10) not null default 0;

8. Foreign Keys

Foreign keys enforce referential integrity but require InnoDB. In high‑traffic internet systems, many prefer to avoid foreign keys, stored procedures, and triggers for performance reasons.

9. Indexes

Besides the primary key, create ordinary indexes for columns frequently used in queries. Limit the number of indexes per table to around 5; if more are needed, consider composite indexes and apply the left‑most prefix rule.

10. Time Fields

Use datetime for most date‑time values (range 1000‑01‑01 to 9999‑12‑31) and avoid varchar for timestamps to enable index usage. Do not set default 0000-00-00 00:00:00 as it can cause conversion errors.

11. Monetary Fields

Prefer decimal(m,n) for amounts to avoid precision loss; float and double are not suitable for financial data.

12. Unique Indexes

Unique indexes are common; ensure indexed columns do not contain NULL values, otherwise uniqueness may be violated.

13. Character Set

Use utf8mb4 for full Unicode support, including emojis. utf8 uses 3 bytes per character and cannot store 4‑byte characters.

14. Collation

Set COLLATE according to case‑sensitivity needs. utf8mb4_general_ci is case‑insensitive, while utf8mb4_bin is case‑sensitive.

15. Large Columns

For large text fields, prefer varchar with a reasonable length limit (e.g., 500 characters) over text to save space. Extremely large data (e.g., contracts) should be stored in a document store like MongoDB, with only the reference ID kept in MySQL.