This Gave Me Chills! How Could My Colleague Design a Database Table Like This…

Personal Experience: Why Table Naming Matters

Recently, I joined an outsourcing project with two teammates.

They designed all the table structures early—without me.

When I finally sat down to work, I couldn’t tell what most tables were for just by looking at their names. For example:

DC_COURSE_TESTPAPER         # Questionnaire under a course
DC_COURSE_PAPER_HISTORY     # Which students received the questionnaire
DC_COURSE_PAPER_HISTORY_ALL # What questions the questionnaire contained

Could you guess the meaning from these names alone?

I couldn’t—and I was shocked.

Conclusion:

Even experienced developers sometimes fail to design clear, meaningful database table names.

---

1. Table Names — Understandable at First Glance

1.1 Meaningful Prefix + Clear Table Name

Prefixes are useful in large systems to distinguish modules—but they must be explicit and understandable.

Poor Examples:

DC_COURSE_TESTPAPER      # TESTPAPER? Questionnaire or exam?
TB_USER_INFO             # TB prefix meaningless, INFO too vague
T_ORDER_DTL              # DTL = “detail”? Not obvious
DATA_TBL_001             # Completely indecipherable

Better Examples:

DC_COURSE_QUESTIONNAIRES # DC = Distance Course System
SYS_USER_PROFILES        # SYS = system core module
ORDER_ITEMS              # Items in an order
LMS_STUDENT_SCORES       # LMS = Learning Management System

When to Use Prefixes:

  • Shared database with multiple business systems → `USER_`, `ORDER_`, `PRODUCT_`
  • Distinguish data types → `LOG_`, `CONFIG_`, `TEMP_`
  • Module separation → `CRM_`, `ERP_`, `CMS_`

---

1.2 Use Full English Words, Not Pinyin

Poor Examples:

kecheng_wenjuan      # Pinyin
user_xinxi           # Mixed Chinese & English
订单_items            # Mixed languages

Better Examples:

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

Why English?

  • Universal in programming
  • Avoids encoding issues
  • Pinyin fields may be acceptable for specialized terms, but table names should remain English

---

1.3 Table Names Show Business Meaning — Not Just Tech

Poor Examples:

data_table_001
temp_storage
middle_table
relation_mapping

Better Examples:

student_scores      # Grades
file_uploads        # Upload records
course_enrollments  # Registrations
user_preferences    # Settings

---

2. Field Naming — Readable at a Glance

2.1 Boolean Fields: Use `is_` Prefix

Poor Examples:

active    # May mean "state" or "action"
delete    # Action or status?
flag      # Too vague

Better Examples:

is_active    # Activated?
is_deleted   # Deleted?
is_verified  # Verified?

Advantages:

  • Clearly boolean type
  • Avoids ambiguity
  • Improves code readability (`if user.is_active`)

---

2.2 Time Fields: Use `_at` Suffix

Poor Examples:

create_time
update_date
delete_at
register_datetime

Better Examples:

created_at
updated_at
deleted_at
registered_at

Benefits:

  • Instantly recognizable as timestamp
  • Avoids mixed styles (`_time`, `_date`, `_datetime`)
  • `_at` semantically precise

Logical Deletion Best Practice:

Use `deleted_at` instead of `is_deleted`.

---

2.3 Other Field Best Practices

  • Use enum values for status fields: `'pending'`, `'approved'`, `'rejected'`
  • Use decimal for money fields: `DECIMAL(10,2)` instead of float/double
  • Store encrypted passwords: `password_hash`
  • Always suffix foreign keys with `_id`

---

3. Table Structure Design — Relationships and Redundancy

3.1 One-to-Many: FK on the “many” side

Users Table (`users`):

| Field | Type | Description |

|-------|------------|--------------|

| id | BIGINT | PK |

| username | VARCHAR | Name |

| email | VARCHAR | Email |

| created_at | TIMESTAMP | Created |

Orders Table (`orders`):

| Field | Type | Description |

|-------|---------|-------------|

| id | BIGINT | PK |

| user_id | BIGINT| FK to User |

---

3.2 Many-to-Many: Meaningful Junction Table Names

Business Meaning Case:

`course_enrollments`

| id | BIGINT | PK |

| student_id | BIGINT | Student FK |

| course_id | BIGINT | Course FK |

| enrolled_at| TIMESTAMP| Date |

| status | ENUM | Status |

Pure Mapping Case:

`user_role_mappings`

| user_id | BIGINT |

| role_id | BIGINT |

---

3.3 Appropriate Redundancy

Add fields like `user_id` in `order_items` for faster queries without joins.

---

4. Original Case Redesign — Clear Naming

Before:

DC_COURSE_TESTPAPER
DC_COURSE_PAPER_HISTORY
DC_COURSE_PAPER_HISTORY_ALL

After:

  • `course_questionnaires` – questionnaire metadata
  • `questionnaire_assignments` – which students received it
  • `questionnaire_questions` – questions in the questionnaire

---

Final Takeaways

  • Code is for humans to read; table structures are for humans to use.
  • Clear, consistent naming improves collaboration, maintainability, and onboarding.
  • Invest time upfront in table & field naming — it saves time later.

---

> 💡 If your database connects with multi-platform content systems, keep names consistent to simplify integration. Open-source tools like AiToEarn官网 help creators and teams use AI to generate, publish, and monetize content across platforms (Douyin, Kwai, WeChat, Bilibili, Facebook, Instagram, LinkedIn, Threads, YouTube, Pinterest, X).

> Whether in schema design or content publishing, clarity + structure = scalability.

---

Checklist for Good Database Naming:

  • [ ] Use meaningful prefixes when needed
  • [ ] Avoid abbreviations that create guesswork
  • [ ] Use full English words, not Pinyin
  • [ ] Table names reflect business meaning
  • [ ] Boolean fields start with `is_`
  • [ ] Time fields end with `_at`
  • [ ] Foreign keys end with `_id`
  • [ ] Status fields use enums
  • [ ] Monetary fields use decimal
  • [ ] Encrypt sensitive fields
  • [ ] Consider redundancy for query efficiency

Read more

Higher IQ, Faster Thinking! Ant Open-Sources Latest Trillion-Parameter Language Model with Multiple Complex Reasoning SOTAs

Higher IQ, Faster Thinking! Ant Open-Sources Latest Trillion-Parameter Language Model with Multiple Complex Reasoning SOTAs

Ant Group Releases Trillion-Parameter Ling-1T Model Another trillion-parameter-level Chinese open-source model has arrived! Just moments ago, Ant Group officially unveiled the first flagship model in its BaiLing series: Ling‑1T, a general-purpose language model with 1 trillion parameters. --- Key Highlights at Launch * Outperforms top open-source models — DeepSeek‑V3.1‑

By Honghao Wang