15 Essential Database Table Design Tips Every Backend Engineer Should Know

Preface

For backend developers, accessing a database is an indispensable part of the code. Core user data is usually stored in databases such as MySQL or Oracle for safety. Daily work involves creating databases and tables to meet business needs, with table creation being the focus of this article.

If table details are not carefully designed, maintenance costs can become high and pitfalls easy to encounter after the system goes live. Below are 15 small tips for creating database tables.

1. Naming

Giving good names to tables, columns, and indexes is crucial.

1.1 Meaningful Names

A good name is concise, expressive, and improves communication and maintenance.

Tip: Keep names within 30 characters.

1.2 Case

Prefer lowercase letters for better readability.

1.3 Separator

Use an underscore _ to separate words in multi‑word identifiers.

1.4 Table Names

Include a business prefix, e.g., order_ for order‑related tables or product_ for product‑related tables, to facilitate grouping and avoid name collisions.

1.5 Column Names

Standardize column names (e.g., use status instead of mixing flag and status), add suffixes like _id or _sys_no for foreign keys, and unify common fields such as create_time, update_time, and delete_status.

1.6 Index Names

Use prefixes: ix_ for normal or composite indexes (e.g., ix_product_status) and ux_ for unique indexes (e.g., ux_product_code).

2. Field Types

Select appropriate field types: date/datetime/timestamp for time, varchar/char/text for strings, int/bigint/smallint/tinyint for numbers. Choose the smallest type that satisfies business requirements to save storage.

Prefer smaller storage space when possible.

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

Use bit for boolean fields.

Use tinyint for enumerations.

Use bigint for primary keys.

Use decimal for monetary values.

Use timestamp or datetime for time fields.

3. Field Length

Pay attention to the length of varchar (character count) versus numeric types (byte size). For example, bigint always occupies 8 bytes regardless of the display width specified.

4. Number of Columns

Avoid creating tables with too many columns; keep the column count under 20. If a table becomes too wide, consider splitting it into smaller related tables.

5. Primary Key

Always define a primary key; it provides the most efficient index. Use AUTO_INCREMENT for single‑node databases, or an external algorithm (e.g., Snowflake) for distributed systems. Keep primary keys business‑agnostic.

6. Storage Engine

In MySQL 5.1 and later, InnoDB is the default engine and is recommended for most cases because it supports transactions and foreign keys. MyISAM may be used for read‑heavy tables but lacks these features.

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 data consistency but can impact performance, especially with many tables. In high‑traffic internet systems, it is often better to avoid foreign keys and rely on application‑level checks.

9. Indexes

Create primary key indexes and additional normal indexes for columns used in queries. Limit the number of indexes per table to around 5; use composite indexes when appropriate and follow the left‑most prefix rule.

10. Time Fields

Prefer datetime over timestamp for broader range and timezone independence. Avoid using varchar for dates if range queries are needed.

Do not set default time values to 0000-00-00 00:00:00 as it may cause query errors.

11. Monetary Fields

Use decimal (e.g., decimal(10,2)) to store money to avoid precision loss associated with float or double.

12. Unique Indexes

Unique indexes are common for fields like organization codes. Ensure indexed columns do not contain NULL values, otherwise uniqueness may be violated.

13. Character Set

Prefer utf8mb4 for full Unicode support, especially for emojis. utf8 uses 3 bytes and cannot store 4‑byte characters.

14. Collation

Set appropriate COLLATE values. utf8mb4_general_ci is case‑insensitive, while utf8mb4_bin is case‑sensitive. Choose based on business needs.

15. Large Fields

For large text such as comments, use varchar with a length limit instead of text to save space. For very large data (e.g., contracts), store the content in a document store like MongoDB and keep only a reference ID in MySQL.