Why Database Schema Design Should Come Before Backend Development

When starting a backend project, it is easy to focus immediately on creating APIs, writing controllers, and implementing business logic.

But before writing backend code, one important step deserves attention: database design.

A well-planned database structure helps developers understand how information is stored, how different entities are connected, and how the application will manage data.

During my development experience, working with ERP modules helped me understand why database schema design is an important part of backend development.

Here are some things I learned.

1. Understand the Requirements Before Designing Tables

The first step in database design is understanding what the application needs to do.

For example, consider a simple billing application. It may need to manage:

• Customers
• Invoices
• Invoice items
• Payments

Before creating tables, it is important to understand how these entities interact.

Questions worth considering include:

• Can one customer have multiple invoices?
• Can an invoice contain multiple items?
• Can an invoice have multiple payment records?
• Which information should be stored in each table?

Understanding these requirements helps avoid unnecessary changes later.

2. Identify Entities and Their Relationships

Once the requirements are clear, the next step is identifying the main entities and their relationships.

For example, in a billing system:

• One customer can have multiple invoices.
• One invoice can contain multiple invoice items.
• One invoice can have multiple payment records.

These relationships help determine how tables should be structured.

A simplified relationship diagram might look like this:

Customer
   |
   | One-to-Many
   |
 Invoice
   |
   | One-to-Many
   |
Invoice Items

Invoice
   |
   | One-to-Many
   |
 Payments

Visualising these relationships makes it easier to understand the database structure before implementation.

3. Understand Primary Keys and Foreign Keys

Primary keys and foreign keys are fundamental to relational database design.

A primary key uniquely identifies a record in a table.

A foreign key establishes a relationship between records in different tables.

For example:

• customer_id can be the primary key in the Customer table.
• invoice_id can be the primary key in the Invoice table.
• customer_id in the Invoice table can reference the Customer table.

This relationship helps maintain data integrity and ensures that an invoice is associated with a valid customer.

4. Consider Constraints and Data Integrity

Database design is not only about creating tables and connecting them.

It is also important to decide what data should be accepted and what rules should be enforced.

Some commonly used constraints include:

• NOT NULL: Prevents a column from storing NULL values.
• UNIQUE: Prevents duplicate values in a column or combination of columns.
• PRIMARY KEY: Uniquely identifies each record.
• FOREIGN KEY: Maintains relationships between tables.
• CHECK: Ensures that values satisfy a specified condition.

For example, an invoice amount should not be negative if the business rules do not allow it.

Applying suitable constraints helps prevent invalid data from entering the database.

5. Use ER Diagrams to Visualise the Structure

An Entity Relationship Diagram (ERD) is a useful way to represent database entities and their relationships.

Instead of trying to understand the entire structure from table definitions, developers can view the relationships visually.

During my development work, I explored ER diagram design using dbdiagram.io.

It helped me organise entities, identify relationships, and understand how foreign keys connect different parts of the application.

Tools such as dbdiagram.io are particularly useful when planning relational database structures.

6. How Database Design Helps Backend Development

A clear database structure makes backend implementation easier to organise.

For example, when working with NestJS and TypeORM, database entities and their relationships influence how developers structure their models and database operations.

A well-planned schema can help with:

• Defining TypeORM entities.
• Establishing entity relationships.
• Writing queries and joins.
• Implementing API operations.
• Maintaining data consistency.
• Understanding how information flows across modules.

It also makes it easier to discuss the system structure with other developers before implementation begins.

7. Lessons I Learned

Working with database-driven ERP applications helped me appreciate the importance of planning before coding.

Some key takeaways:

• Understand the business requirements first.
• Identify entities and relationships clearly.
• Plan primary keys and foreign keys.
• Consider constraints and data integrity.
• Visualise the schema using an ER diagram.
• Review the structure before implementing backend logic.

Database design is not a one-time activity. As application requirements change, the schema may also need to evolve.

Final Thoughts

A good backend application starts with a clear understanding of its data.

Taking time to plan the database structure before implementing APIs can make development more organised and help reduce avoidable changes later.

My experience working with PostgreSQL, TypeORM, and ERP modules has helped me understand how important database design is to building maintainable applications.

Do you usually create an ER diagram before starting backend development, or do you design the database as you build the application?

I'd love to hear how other developers approach it.

Further Reading

• PostgreSQL Documentation - Constraints
• dbdiagram.io
• TypeORM Documentation - Entities

The examples in this article are simplified illustrations of relational database concepts.