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