A Database Schema Is a Product Decision, Not Just a Backend Detail

When I started thinking more seriously about FinLedger, I realized that database design isn't simply about deciding which columns to create.

The schema determines what your application can represent easily, what becomes difficult later, and how safely your data can evolve.

For a lending and borrowing application, this matters a lot.

Consider a simple requirement:

A person can lend money, borrow money, repay it, and have the transaction remain available in their history.

It sounds straightforward.

But several questions immediately appear.

Should a person and their transaction be stored in the same table?

Should every repayment modify the original transaction?

Should repayments be separate records?

What happens when the outstanding balance reaches zero?

Can the same person have another transaction later?

These decisions affect the entire application.

Separate entities from events

One approach is to think about the difference between who and what happened.

For example:

Person
  |
  └── Transactions
          |
          ├── Lending
          ├── Borrowing
          └── Repayment

The person represents an entity.

The transaction represents an event involving that entity.

Keeping those concepts separate gives the application more flexibility.

Instead of treating the current balance as the only important information, the application can maintain a history of what actually happened.

Current state vs history

This is one of the things I've been paying more attention to.

Suppose someone owes ₹5,000.

Later they repay ₹2,000.

The current balance is now ₹3,000.

But the application may still need to know:

Original transaction: ₹5,000
Repayment:            ₹2,000
Remaining balance:    ₹3,000

If we only store:

balance = 3000

we've lost information.

The current state is useful, but the history explains how we arrived at that state.

That's an important distinction when designing financial or transaction-oriented applications.

Don't design only for today's screen

A common mistake is designing the database around the current UI.

For example:

Name
Amount
Type
Balance

might be enough to display a simple screen.

But requirements rarely stay that simple.

Soon you may need:

• Multiple transactions for the same person
• Partial repayments
• Complete repayment
• Transaction history
• Editing
• Deletion
• Search
• Filtering
• Dates
• Notes
• Reports

If the schema wasn't designed with those concepts in mind, adding them later can become unnecessarily painful.

Relationships matter

Another lesson I'm learning is that IDs are more important than names.

If two records both contain:

name = "Rahul"

the name isn't a reliable way to identify the same person.

A database should have stable identifiers:

Person
id = 42

Transaction
person_id = 42

Now the relationship is explicit.

The name can change.

The ID should remain stable.

Good schema design reduces application complexity

A good database doesn't just store data.

It gives the rest of the application a clean model to work with.

When the relationships and rules are clear, the Android layer, backend layer, and business logic become easier to reason about.

That's why I'm starting to see database design as part of product design.

Before writing the UI, it's worth asking:

What entities exist?
What events can happen?
What relationships exist?
What information must never be lost?
What can change?
What must remain stable?

Those questions are often more valuable than immediately creating the first screen.

I'm still learning this through building FinLedger, but one principle is becoming clear:

A database schema isn't just where your data lives.

It's a model of how your product works.

Get that model wrong, and you'll spend a lot of time fighting your own application later.

database #backend #systemdesign #android #softwarearchitecture #kotlin #buildinpublic