How to Name Database Tables and Fields for Clear, Maintainable Schemas

Why Naming Matters

When joining a project where the table structure was designed by others, you may find cryptic names that make the schema impossible to understand at a glance. Clear, self‑describing names reduce onboarding friction and prevent mistakes.

1. Table Naming – Meaningful Prefixes + Clear Names

Bad examples :

DC_COURSE_TESTPAPER   # TESTPAPER is a questionnaire or an exam?
TB_USER_INFO          # INFO is too generic
T_ORDER_DTL           # DTL is an abbreviation for detail?
DATA_TBL_001          # No meaning at all

Good examples :

DC_COURSE_QUESTIONNAIRES   # DC = Distance Course system
SYS_USER_PROFILES        # SYS = core system module
ORDER_ITEMS              # Order item details
LMS_STUDENT_SCORES       # LMS = Learning Management System

The difference is that good names use a clear business prefix and full English words, while bad names rely on obscure abbreviations.

When to Use Prefixes?

Multiple business systems share a database (e.g., USER_, ORDER_, PRODUCT_).

Distinguish data types such as logs, configs, or temporary tables ( LOG_, CONFIG_, TEMP_).

Large projects with clear modules (e.g., CRM_, ERP_, CMS_).

2. Use Full English Words, Not Pinyin or Mixed Language

Bad examples :

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

Good examples :

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

English is the lingua franca of programming; using it avoids encoding problems and makes the schema accessible to all developers.

3. Boolean Fields – Prefix with is_

Bad examples :

active   # Is it active or does it mean "active" action?
delete   # Deletion status or deletion action?
flag     # What flag?

Good examples :

is_active   # Clearly a boolean flag
is_deleted  # Deletion status
is_verified # Verification status

Using is_ makes the field’s boolean nature obvious, improves readability, and prevents ambiguity.

4. Timestamp Fields – Unified _at Suffix

Bad examples :

create_time
update_date
delete_at
register_datetime

Good examples :

created_at   # Creation time
updated_at   # Update time
deleted_at   # Deletion time (useful for logical deletes)
registered_at # Registration time

Benefits:

Instantly recognizable as a time field.

Consistent naming avoids mixed _time, _date, _datetime conventions. _at expresses a specific point in time, which is more precise in English.

Logical Delete Best Practice

Prefer a deleted_at timestamp over a boolean is_deleted. It records when a row was removed, supports recovery (set to NULL), and enables historical queries.

5. Foreign‑Key Fields – Unified _id Suffix

Bad examples :

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

Good examples :

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

This eliminates confusion and makes JOIN conditions straightforward.

6. Redundant Fields – When to Duplicate IDs

Redundancy can simplify queries and improve performance when:

Cross‑level lookups are frequent (e.g., needing the user ID directly in an order‑items table).

Reporting or aggregation queries run often on the redundant column.

The relationship is read‑heavy and write‑light.

The higher‑level ID rarely changes.

Example: adding user_id to order_items lets you fetch a user’s purchase history without joining the orders table.

7. Relationship Tables – Naming Depends on Business Meaning

Business‑rich many‑to‑many (e.g., student‑course enrollment with status, enrollment date):

student_course_enrollments   # Contains extra fields like status, enrolled_at

Pure mapping tables (no extra attributes):

student_course_mappings   # Simple ID‑to‑ID mapping

Choose names that reflect the purpose: enrollments, orders, friendships for business‑rich tables; mappings or relations for pure joins.

8. Re‑designing the Original Example

The original cryptic tables:

DC_COURSE_TESTPAPER
DC_COURSE_PAPER_HISTORY
DC_COURSE_PAPER_HISTORY_ALL

are replaced with clear, purpose‑driven tables:

Course Questionnaires (course_questionnaires)

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

Questionnaire Assignments (questionnaire_assignments)

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

Questionnaire Questions (questionnaire_questions)

id               BIGINT   # Primary key
questionnaire_id BIGINT   # Questionnaire ID
content          TEXT     # Question text
question_type    ENUM('single','multiple','text')
sort_order       INT      # Order in questionnaire

Now the table names immediately convey their purpose, and column names follow the conventions described above.

Conclusion

Good database design starts with clear, self‑describing table and column names. Use meaningful prefixes, full English words, consistent is_ for booleans, _at for timestamps, and _id for foreign keys. Add redundancy only when it simplifies frequent queries. Investing time in a well‑named schema pays off in reduced communication overhead, easier onboarding, and more maintainable code.

Remember: code is written for people to read; database schemas are built for people to use.