18 Essential Tips for Designing Robust Database Tables

Introduction

For backend developers, accessing a database is an indispensable part of coding. This article shares 18 practical tips for creating tables in MySQL (and similar databases) to avoid common pitfalls and reduce maintenance costs.

1. Naming

1.1 Meaningful Names

A good table, column, or index name should be concise, self‑explanatory, and pleasant to read; a bad name is ambiguous and hard to understand.

Bad example:

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

Good example:

用户名称字段定义成：user_name

Keep names under 30 characters.

1.2 Use Lowercase

Prefer all‑lowercase letters because they are easier to read.

Bad example: PRODUCT_NAME、PRODUCT_name Good example:

product_name

1.3 Separator

When a name contains multiple words, use an underscore ( _) as the separator.

Bad example:

productname、productName、product name、product@name

Good 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).

1.5 Column Names

Standardize common columns: status for state, *_id or *_sys_no for foreign keys, create_time, update_time, delete_status, etc.

1.6 Index Names

Primary key: id or sys_no. Ordinary indexes: prefix with ix_ (e.g., ix_product_status). Unique indexes: prefix with ux_ (e.g., ux_product_code).

2. Field Types

Choose the smallest type that satisfies business needs: tinyint for small integers, char vs varchar based on length variability, bit for boolean, bigint for primary keys, decimal for monetary values, datetime or timestamp for dates.

Prefer the smallest storage size that meets requirements.

Use char for fixed‑length strings, varchar for variable length.

Use bit for boolean fields.

Use tinyint for enum‑like fields.

Use bigint for primary keys.

Use decimal for monetary amounts.

Use timestamp or datetime for time fields.

3. Field Length

Remember that varchar and char specify character length, while most other types (e.g., int, bigint) specify byte length.

4. Number of Columns

Avoid tables with dozens or hundreds of columns; keep the column count below 20 per table. Split very large tables into smaller ones with the same primary key if needed.

5. Primary Key

Always define a primary key; it provides the most efficient index. In a single‑node MySQL use AUTO_INCREMENT. In distributed databases generate IDs externally (e.g., Snowflake) to guarantee global uniqueness.

6. Storage Engine

MySQL 8 defaults to InnoDB, which supports transactions and foreign keys. MyISAM is faster for read‑heavy tables but lacks these features. Generally use the default InnoDB.

7. NOT NULL

Define columns as NOT NULL whenever possible to save space and avoid index issues. Provide default values for newly added NOT NULL columns to prevent insert failures.

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

8. Foreign Keys

Foreign keys enforce data consistency but require InnoDB. They can cause performance problems in highly relational systems; many large‑scale services avoid them in favor of application‑level checks.

9. Indexes

Besides the primary key, create ordinary indexes for columns used in joins or filters. Limit the number of indexes per table to about 5; consider composite indexes when necessary.

10. Time Fields

Prefer datetime over timestamp for a larger range and no timezone dependence. Avoid using 0000-00-00 00:00:00 as a default value.

11. Monetary Fields

Use decimal(m,n) to store money to avoid precision loss; float and double are not recommended.

12. JSON Fields

When a column needs to store flexible data, use MySQL's json type, which allows easy storage and querying of structured JSON.

13. Unique Indexes

Unique indexes prevent duplicate values but fail if any indexed column contains NULL. Ensure indexed columns are NOT NULL.

14. Character Set

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

15. Collation

Collation determines case sensitivity. utf8mb4_general_ci is case‑insensitive, while utf8mb4_bin is case‑sensitive. Choose based on business requirements.

16. Large Fields

For large text, use varchar with an appropriate length instead of text to improve storage efficiency. Extremely large binary data (e.g., contracts) should be stored outside MySQL, such as in MongoDB, with only a reference ID kept in the relational table.

17. Redundant Fields

Denormalize by adding frequently accessed data (e.g., user name) directly to the main table to avoid costly joins, but be aware of extra storage and potential consistency issues.

18. Comments

Always add clear comments to tables and columns to describe purpose and possible values; this greatly aids future maintenance.