Master Database Table Naming: Clear, Consistent, and Meaningful Designs

Personal Experience

While working on an outsourced project, I inherited a database schema I hadn't helped design; the table names were cryptic, making it impossible to understand the data at a glance.

This experience highlighted the need for well‑designed table structures even in "advanced" development.

Table Naming: First Glance Should Reveal Purpose

1. Meaningful Prefix + Clear Table Name

In large systems, prefixes help distinguish modules, but they must have clear meaning and the table name should be semantically clear.

❌ Bad Naming

DC_COURSE_TESTPAPER   # Is TESTPAPER a questionnaire or an exam?
TB_USER_INFO         # TB prefix is meaningless, INFO is too generic
T_ORDER_DTL           # DTL is an abbreviation for detail?
DATA_TBL_001          # Completely unreadable

✅ Good Naming

DC_COURSE_QUESTIONNAIRES   # DC indicates Distance Course system
SYS_USER_PROFILES         # SYS denotes core system module
ORDER_ITEMS               # Order item details
LMS_STUDENT_SCORES        # LMS stands for Learning Management System

The difference: bad names rely on obscure abbreviations, while good names use clear prefixes and full English words.

When to Use Prefixes?

Multiple business systems sharing a database: USER_, ORDER_, PRODUCT_ Distinguish data types: LOG_, CONFIG_, TEMP_ Large project module division: CRM_, ERP_,

CMS_

2. Use Full English Words Instead of Pinyin

❌ Bad Naming

kecheng_wenjuan   # Pinyin
user_xinxi        # Mixed Chinese and English
订单_items         # Mixed Chinese and English

✅ Good Naming

course_questionnaires   # Pure English, clear meaning
user_profiles          # Pure English
order_items            # Pure English

Reason: English is the lingua franca of programming, making it easier for team members to understand and avoiding encoding issues.

3. Table Names Should Reflect Business Meaning, Not Just Technical Implementation

❌ Bad Naming

data_table_001
temp_storage
middle_table
relation_mapping

✅ Good Naming

student_scores      # Student grades
file_uploads       # File upload records
course_enrollments  # Course registrations
user_preferences   # User preference settings

Only use generic names for temporary or purely technical tables.

Field Naming: Names Should Reveal Intent

1. Boolean Fields Start with is_

❌ Bad Naming

active   # Is it activated or active?
delete   # Deletion status or action?
flag     # What flag?

✅ Good Naming

is_active   # Whether active
is_deleted  # Whether deleted
is_verified # Whether verified

Benefits: Immediate boolean meaning, avoids ambiguity, improves code readability.

2. Time Fields Use a Unified _at Suffix

❌ Bad Naming

create_time
update_date
delete_at
register_datetime

✅ Good Naming

created_at   # Creation time
updated_at   # Update time
deleted_at   # Deletion time
registered_at # Registration time

Advantages: One‑glance identification, consistent naming, easier querying, and logical deletion best practice using deleted_at.

3. Foreign Key Fields End with _id

❌ Bad Naming

user        # Is it user ID or object?
course      # Course ID?
teacher_key # What key?

✅ Good Naming

user_id    # User ID
course_id  # Course ID
teacher_id # Teacher ID

Additional Practical Tips

Logical deletion should use a timestamp field ( deleted_at) instead of a boolean, allowing you to see when a record was removed and to restore it by setting the timestamp to NULL.

Status fields should use enum with descriptive values (e.g., 'pending', 'approved', 'rejected') rather than numeric codes.

Monetary fields should use decimal(10,2) instead of float or double to avoid precision issues.

Password fields must be stored as hashes, e.g., password_hash or encrypted_password, never as plain password.

For frequently queried logical deletions, add an index on deleted_at to improve performance.

Table Structure Design: Clear Relationships and Appropriate Redundancy

1. One‑to‑Many: Foreign Key on the “many” Side

Example: Users and Orders.

users table

id          BIGINT   # Primary key
username    VARCHAR(50)
email       VARCHAR(100)
created_at  TIMESTAMP

orders table

id          BIGINT   # Primary key
user_id     BIGINT   # Foreign key to users
order_no    VARCHAR(32)
total_amount DECIMAL(10,2)
status      VARCHAR(20)
created_at  TIMESTAMP

Benefits: Directly know which user an order belongs to, simple JOIN, and adding order fields doesn't affect the users table.

2. Many‑to‑Many: Intermediate Table Name Should Reflect the Relationship

Case 1 – Business‑Rich Relationship

student_course_enrollments   # Stores enrollment time, status, etc.

Fields: student_id, course_id, enrolled_at, status.

Case 2 – Pure Mapping

user_role_mappings   # Simple many‑to‑many between users and roles

Fields: user_id, role_id.

Key: Use business‑meaningful names (e.g., enrollments, orders) when the relationship carries extra data; otherwise, generic names like mappings or relations are fine.

3. Appropriate Field Redundancy Improves Query Efficiency

Redundant fields can avoid costly JOINs when they are read‑heavy and write‑light.

order_items table (redundant user_id )

id          BIGINT   # Primary key
order_id    BIGINT   # Order reference
user_id     BIGINT   # Redundant user ID for quick lookup
product_id  BIGINT
quantity    INT
price       DECIMAL(10,2)

Benefits: Directly query a user's purchase history without joining the orders table.

product_reviews table (redundant category_id )

id          BIGINT   # Primary key
product_id  BIGINT
category_id BIGINT   # Redundant category for fast aggregation
user_id     BIGINT
rating      TINYINT
content     TEXT

Redundancy is suitable when the field is frequently used in reports, hierarchical queries, or when the related ID rarely changes.

Revisiting the Original Example

Original cryptic tables:

DC_COURSE_TESTPAPER
DC_COURSE_PAPER_HISTORY
DC_COURSE_PAPER_HISTORY_ALL

Redesigned tables with clear naming:

course_questionnaires

id          BIGINT   # Primary key
course_id   BIGINT   # Course ID
title       VARCHAR(200)
description TEXT
status      ENUM('draft','published','closed')
created_at  TIMESTAMP

questionnaire_assignments

id                BIGINT   # Primary key
questionnaire_id BIGINT   # Questionnaire ID
student_id        BIGINT   # Student ID
assigned_at       TIMESTAMP
status            ENUM('assigned','started','completed')

questionnaire_questions

id                BIGINT   # Primary key
questionnaire_id BIGINT   # Questionnaire ID
content           TEXT
question_type     ENUM(...)
sort_order        INT

Now the purpose of each table is obvious at a glance.

Conclusion

Database schema design is critical for project success. Clear naming conventions for tables and fields reduce communication overhead, simplify onboarding, and make maintenance easier. Investing time in a well‑thought‑out schema pays off in the long run.

“Code is written for humans to read; table structures are built for people to use.”