MySQL Table Design: 14 Golden Rules

1. Include These Common Fields When Designing a Table

Generally, a table should include the following fields:

• id: Primary key. Every table must have a primary key — no exceptions.
• create_time: Creation time — required.
• modified_time: Last modified time — required. This should be updated whenever the record is updated.
• version: Version number of the record, typically used for optimistic locking — optional.
• modifier: The person who last modified the record — optional.
• creator: The person who created the record — optional.

2. Every Field Should Have Comments, Especially for Enumerations

When designing a table, every field should include a comment. This is especially important for fields that use enumerations — all enum values should be listed in the comment. If changes occur later, they should be reflected in the comments as well.

Bad Example:

CREATE TABLE order_tab (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNIQUE,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    payment_status VARCHAR(20) DEFAULT 'not_paid',
    version INT DEFAULT 0,
    created_time DATETIME,
    updated_time DATETIME,
    creator VARCHAR(255),
    modifier VARCHAR(255)
);

Good Example:

CREATE TABLE order_tab (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique identifier for the order item, auto-incremented primary key',
    order_id BIGINT UNIQUE COMMENT 'Globally unique identifier for the order',
    user_id BIGINT NOT NULL COMMENT 'Unique identifier for the user, linked to the user table',
    total_amount DECIMAL(10, 2) NOT NULL COMMENT 'Total order amount, precise to two decimal places',
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'Order status, e.g., PENDING, COMPLETED, etc.',
    payment_status VARCHAR(20) DEFAULT 'not_paid' COMMENT 'Payment status, e.g., not_paid, paid, etc.',
    version INT DEFAULT 0 COMMENT 'Optimistic lock version number for concurrency control',
    created_time DATETIME COMMENT 'Order creation timestamp',
    updated_time DATETIME COMMENT 'Timestamp of last update',
    creator VARCHAR(255) COMMENT 'Creator of the order — typically the username of the user or system',
    modifier VARCHAR(255) COMMENT 'Last person or system to modify the order'
);

3. Naming Conventions

Use clear and consistent naming conventions for table names, field names, index names, etc. Good naming improves readability and understanding.

For example, avoid names like:

• acc_no, 1_acc_no (bad examples)

Instead, use:

• account_no, account_number (good examples)

Additional rules:

• Table and field names should only use lowercase letters or numbers.
• Do not start names with numbers.
• Avoid abbreviations.
• Index naming conventions:
  • Primary key: pk_<field_name>
  • Unique index: uk_<field_name>
  • Regular index: idx_<field_name>

4. Choose Appropriate Field Types

When designing a table, choose the most appropriate field types:

• Use the smallest suitable data type to save storage — e.g., prefer tinyint, smallint, int, bigint in that order.
• For monetary values, use decimal instead of float or double.
• If the string length is fixed or nearly fixed, use char.
• Use varchar for variable-length strings, but keep the length under 5000.
• For very large values, consider using text and storing them in a separate table linked by the primary key.
• The combined length of all varchar fields in a table must not exceed 65535 bytes. If needed, use TEXT or LONGTEXT types.

5. Design Reasonable Primary Keys

Avoid binding primary keys to business logic. For instance, using a user ID (even if unique) as the primary key is not recommended. Instead, use a meaningless but unique identifier such as:

• A UUID,
• An auto-incrementing primary key,
• A primary key generated by a Snowflake algorithm.

6. Choose Appropriate Field Lengths

Let me ask you a question first: In a database, does the field length represent character length or byte length?

In MySQL:

• varchar and char specify character length.
• Other types typically specify byte length.

For example:

• char(10) means 10 characters.
• bigint(4) refers to the display width (not storage size), but bigint always takes 8 bytes regardless.

When designing tables, carefully consider field length. For example, for a username field expected to be between 5 and 20 characters, you might define it as username varchar(32).

Tip: Field lengths are generally best set as a power of 2 (i.e., 2ⁿ).

7. Prefer Logical Deletion Over Physical Deletion

Physical Deletion: Data is permanently removed from disk, freeing up storage space.

Logical Deletion: Add a field like is_deleted to mark data as deleted.

Example of physical deletion:

DELETE FROM account_info_tab WHERE account_no = '666';

Example of logical deletion:

UPDATE account_info_tab SET is_deleted = 1 WHERE account_no = '666';

Why prefer logical deletion?

• Data recovery is difficult with physical deletion.
• Auto-incremented primary keys become non-contiguous.
• For core business tables, it's better to update status fields rather than physically delete records.

8. Avoid Too Many Fields in a Single Table

When designing a table, try to limit the number of fields — generally no more than 20.

Too many fields can lead to:

• Large row sizes,
• Poor query performance.

If the business logic requires many fields, consider splitting the large table into smaller ones with the same primary key.

When a table has a large number of fields, consider separating it into:

• A "query condition" table (for fast filtering),
• A "detail" table (for full content),
  to improve performance.

9. Use NOT NULL Whenever Possible

Unless there’s a specific reason, it’s recommended to define fields as NOT NULL.

Why?

• Prevents null pointer issues.
• NULL takes up additional storage space.
• Comparisons with NULL are more complex and hinder query optimization.
• NULL values can cause index failures.
• If you can safely default a field to an empty string or constant, you should define it as NOT NULL.

10. Evaluate Which Fields Need Indexes

First, assess the size of your dataset. If a table only has a few hundred rows, indexing may not be necessary.

In general, if a field is frequently used in query conditions, it should have an index. But indexing should not be overused:

• Avoid creating too many indexes — keep it to 5 or fewer per table.
  • Too many indexes slow down insert and update operations.
• Do not index fields with low cardinality (e.g., gender).
• Be aware of cases that cause index failure, such as using built-in MySQL functions on indexed fields.
• To reduce the number of indexes, consider composite (multi-column) indexes.
  • Use techniques like covering indexes and follow the leftmost prefix rule.

Example of a user table:

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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Since you'll likely query by user_id or name, and user_id is unique, you can define indexes like this:

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;

11. Avoid Using MySQL Reserved Words

If a database name, table name, or field name contains a reserved word, it must be wrapped in backticks (`) in SQL statements. This complicates SQL writing and increases the risk of errors, especially when scripting or working with shell variables.

Therefore, avoid using MySQL reserved words such as select, interval, desc, etc.

12. Prefer the InnoDB Storage Engine

When creating tables, you must choose a storage engine. In general, InnoDB is preferred unless your read-write ratio is less than 1%, in which case MyISAM may be considered.

13. Choosing the Right Time Type

When designing tables, we usually include common time-related fields like create_time, modified_time, etc. So which MySQL time type should you use?

Here are the main options:

• date: Stores date values in yyyy-mm-dd format. Range: 1000-01-01 to 9999-12-31. Size: 3 bytes.
• time: Stores time values in hh:mm:ss format. Range: -838:59:59 to 838:59:59. Size: 3 bytes.
• datetime: Stores date and time in yyyy-mm-dd hh:mm:ss format. Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59. Size: 8 bytes. Not time zone dependent.
• timestamp: Stores date and time as a timestamp (yyyymmddhhmmss). Range: 1970-01-01 00:00:01 to 2038-01-19 03:14:07. Size: 4 bytes. Time zone dependent.
• year: Stores year values in yyyy format. Range: 1901 to 2155. Size: 1 byte.

Recommendation: Prefer using the datetime type to store date and time, because it has a larger range and is independent of time zones.

14. Security Considerations

• Data Encryption: Sensitive information like user passwords should be stored in an encrypted format.
• Data Masking: For personally identifiable information (PII) such as phone numbers or email addresses, apply data masking for better privacy and compliance.

---

We are Leapcell, your top choice for hosting backend projects.

Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:

Multi-Language Support

• Develop with Node.js, Python, Go, or Rust.

Deploy unlimited projects for free

• pay only for usage — no requests, no charges.

Unbeatable Cost Efficiency

• Pay-as-you-go with no idle charges.
• Example: $25 supports 6.94M requests at a 60ms average response time.

Streamlined Developer Experience

• Intuitive UI for effortless setup.
• Fully automated CI/CD pipelines and GitOps integration.
• Real-time metrics and logging for actionable insights.

Effortless Scalability and High Performance

• Auto-scaling to handle high concurrency with ease.
• Zero operational overhead — just focus on building.

Explore more in the Documentation!

Follow us on X: @LeapcellHQ