From Application to Architecture: My Journey Learning Database Design

In March 2020, as I became more and more involved in backend development, a field I had rarely touched directly but that is critically important was staring me in the face — database design. I realized that good database design is the cornerstone of building high-performance, highly scalable backend services.

More Than Just Creating Tables

In the past, I was mostly a "consumer" of databases, interacting with them through ORM frameworks such as JPA. But now I needed to think from a "designer's" perspective. I learned that database design is far more than creating a few tables — it is a systematic undertaking that mainly involves the following stages:

1. Requirements analysis: Deeply understand the business requirements and identify the core Entities and the Relationships between them. This is the starting point of the whole design process.
2. Logical design: Turn the results of requirements analysis into a logical model that is independent of any specific database. In this stage I focused on the three normal forms (1NF, 2NF, 3NF) and understood how normalization eliminates data redundancy and keeps data consistent.
3. Physical design: On top of the logical model, carry out the physical implementation tailored to the specifics of the chosen database (e.g., MySQL). This includes choosing appropriate data types, designing efficient indexes, and considering strategies such as partitioning/sharding.
4. Maintenance and optimization: Database design is never set in stone. As the business evolves, the database needs continuous monitoring, tuning, and even refactoring.

Applying It in Practice

In later projects such as "AdRobot", the "Monitor" monitoring system, and the permission management system, I began consciously applying these design principles.

• When designing AdRobot's marketing task tables, I carefully analyzed the one-to-many relationship between "marketing plans" and "push tasks", and built indexes on frequently queried fields such as task status and execution time.
• When designing the permission system, I created three core tables — users, roles, and permissions — and used junction tables to manage the many-to-many relationships between them, achieving flexible access control.
• When designing the logging system, I started thinking about sharding massive log data across tables and databases to keep future query performance solid.

From caring about an app's UI/UX to caring about normal forms and indexes, this learning marked an important leap in my technical perspective. It got me thinking about how to build robust systems from a lower-level, more macroscopic view — a key step from frontend to backend, and from application to architecture.