15 Essential Tips for Designing Robust Database Tables

1. Naming Conventions

Give tables, columns, and indexes meaningful, concise names. Use lowercase letters, separate words with underscores, and keep names under 30 characters. Example of a good column name: user_name. Bad examples include yong_hu_ming, 用户_name, or overly long names.

2. Field Types

Select the smallest suitable data type to save storage while meeting business needs. Use tinyint for small numeric ranges, int or bigint for larger numbers, varchar / char for strings, and date / datetime / timestamp for temporal data. Avoid over‑allocating types that waste space.

3. Field Length

Define explicit lengths, remembering that in MySQL varchar and char represent character length, while other types represent byte length. For bigint(4), the “4” is display width; the actual storage remains 8 bytes.

4. Number of Fields

Limit the number of columns per table to around 20 to maintain query performance. Split overly wide tables into multiple related tables sharing the same primary key.

5. Primary Keys

Always define a primary key, typically id or sys_no. Use AUTO_INCREMENT for simple cases, but in distributed systems prefer globally unique IDs (e.g., Snowflake). Keep primary keys business‑agnostic.

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, the performance gap has narrowed, so using the default InnoDB is recommended.

7. NOT NULL

Define columns as NOT NULL whenever possible to avoid extra storage for nulls, prevent index inefficiencies, and simplify queries. When adding new NOT NULL columns to existing tables, provide sensible default values, e.g.,

ALTER TABLE product_sku ADD COLUMN brand_id INT(10) NOT NULL DEFAULT 0;

.

8. Foreign Keys

Foreign keys enforce referential integrity but can impact performance in highly relational schemas. In large‑scale internet systems, it is often preferable to omit foreign keys and handle consistency at the application layer.

9. Indexes

Create primary‑key indexes automatically; add ordinary indexes for columns used in lookups, e.g., KEY `ix_spu_id` (spu_id) USING BTREE. Limit total indexes per table to about five; consider composite indexes to reduce count. Avoid indexing low‑cardinality columns like status.

10. Time Fields

Prefer datetime for storing dates and times (range 1000‑01‑01 to 9999‑12‑31, timezone‑independent). timestamp uses 4 bytes and is timezone‑aware but limited to 1970‑2038.

11. Money Fields

Use decimal(m,n) for monetary values to preserve precision; avoid float or double. For example, decimal(10,2) stores up to 8 integer digits and 2 decimal places.

12. Unique Indexes

Unique indexes ensure column uniqueness. When creating composite unique indexes, ensure none of the columns contain NULL, otherwise uniqueness may be violated.

13. Character Set

Prefer utf8mb4 for full Unicode support, including emojis. utf8 uses less space but cannot store 4‑byte characters.

14. Collation

Set collation to match case‑sensitivity requirements. utf8mb4_general_ci is case‑insensitive, while utf8mb4_bin is case‑sensitive. Choose appropriately to avoid unexpected query results.

15. Large Fields

For large text (e.g., comments), use varchar with a reasonable length limit instead of text to improve storage efficiency. Extremely large data such as contracts should be stored in a document store (e.g., MongoDB) with only the document ID kept in MySQL.