10 Essential MySQL Table Naming Rules Every Engineer Should Follow

Many companies maintain lengthy MySQL specifications that are hard to enforce; Alibaba’s forced MySQL table creation standard contains only ten concise rules that can be used as a reference.

Rule 1: Boolean Fields

Use the

is_xxx

naming pattern for fields representing a yes/no concept.

Data type must be

unsigned tinyint

.

Store

1

for true and

0

for false.

Example:

is_deleted

(1 = deleted, 0 = not deleted). Incorrect names include

deleted

,

if_deleted

,

delete_or_not

.

Rule 2: Letters and Numbers

Table and column names must be lowercase.

Do not start with numbers, and avoid double underscores that contain only numbers.

Correct:

aliyun_admin

,

level3_name

. Incorrect:

AliyunAdmin

,

level_3_name

.

Rule 3: Singular Table Names

Table names should represent an entity, not a quantity, so avoid plural forms.

Rule 4: Avoid Reserved Words

Do not use MySQL reserved keywords such as

desc

,

range

,

match

,

delayed

, etc.

Rule 5: Index Naming Conventions

Primary key:

pk_xxx

Unique index:

uk_xxx

Regular index:

idx_xxx

Rule 6: Decimal Type Guidelines

Use

decimal

for precise numeric values.

Avoid

float

and

double

due to precision loss.

If the required range exceeds

decimal

, split into integer and fractional parts stored separately.

Rule 7: Fixed‑Length Strings

When string lengths are very close, use fixed‑length

char

to pre‑allocate storage and avoid reallocation.

Rule 8: Variable‑Length Strings

Use

varchar

for long strings with large length variance to save space.

If most strings exceed 5000 characters, store them in a separate table using

text

.

Rule 9: Mandatory Fields

id

:

bigint unsigned

, auto‑increment, step 1, no business meaning.

create_time

:

datetime

(or

timestamp

if timezone info is needed).

update_time

: same type as

create_time

.

Rule 10: No Physical Deletions

Prefer logical deletions to retain data assets and enable operation traceability.

Does your company have a MySQL specification? Do you think these table‑creation rules are reasonable? Share your thoughts. Source: Architect’s Path.