Foreign Keys, Views, and the Real Problem: Database Boundaries

Why this blog exists

This blog is not theory — it’s a reflection of how my understanding evolved while dealing with a real system.

I started with frustration:

• Foreign keys made migrations painful
• Views across databases kept breaking
• Every schema change required chasing dependencies

Initially, I blamed the tools.

But after digging deeper, I realized:

👉 The real problem was not foreign keys or views 👉 It was my lack of clarity on boundaries, ownership, and consistency

This blog captures that shift.

TL;DR

• Start with business rules → ownership → scale
• Use foreign keys only within a database
• Keep transactional logic inside a single DB
• Avoid cross-DB views in core flows
• Use S2S or events for cross-boundary communication

👉 Most problems come from bad boundaries, not features

Problem Statement

I was dealing with:

• Multiple databases per organization
• Views connecting those databases
• Core business logic depending on those views

This caused:

• Migration order issues
• Tight coupling
• Repeated updates across databases

Key question:

👉 Why do systems using foreign keys and views become harder to manage at scale?

My Initial Thinking (Before)

• Foreign keys are annoying — backend can validate data
• Views are just a simple abstraction layer
• Splitting databases improves performance (connections, throughput)

This led to decisions that caused:

• Cross-database dependencies
• Fragile migrations
• Hidden coupling via views

My Updated Understanding (After)

1. Boundary Definition Comes First

My rule now:

Business rules → Ownership → Scale

Earlier, I optimized for scale first (connections, throughput). That was a mistake.

2. Where Integrity Should Live

I clarified this explicitly:

• Within DB → Foreign Keys
• Between services → S2S (application level)
• Distributed → Events / async systems

This removed confusion about “why FKs exist”.

3. Transactional Logic Needs a Single DB

If something requires:

• atomicity
• strong consistency

👉 It should be inside one database

Trying to spread this across DBs is what creates complexity.

4. My Biggest Mistake

I split databases without clearly defining:

• ownership
• boundaries
• data responsibility

Then I used views to glue everything together.

👉 Views became a coupling mechanism, not a convenience

5. Views Are Not the Enemy — Misuse Is

Views should be used for:

• simplifying reads
• abstraction

But I used them for:

• cross-database communication
• core business logic

👉 That’s why migrations became painful

6. Performance Assumption Was Weak

I assumed:

• multiple DBs = better performance

But didn’t validate:

• actual bottlenecks
• real scaling limits

👉 I optimized prematurely

7. The Real Tradeoff (Clear Now)

When accessing data across boundaries:

• Direct DB access (views)

  • low latency
  • high coupling ❌

• S2S

  • clear ownership
  • more latency

• Events / replication

  • local reads
  • eventual consistency

👉 You must choose tradeoffs consciously

Key Learnings

• Foreign keys are useful when boundaries are correct
• Views become dangerous when used across databases
• Migration pain is a symptom of coupling
• Splitting DBs without ownership clarity leads to chaos
• Transactional decisions should not cross DB boundaries

Missing Context (What I Didn’t Know Before)

• Why foreign keys exist beyond validation
• Why views are not meant for cross-system contracts
• How important ownership boundaries are
• That most systems don’t need early DB splitting

How Senior Engineers Approach This

• Define boundaries before scaling
• Keep transactions local
• Use DB constraints where applicable
• Avoid cross-DB coupling
• Use S2S/events for communication

They focus on:

👉 "Who owns this data?"

Not:

👉 "Which feature should I use?"

Migration Impact (My Real Pain)

Because of my design:

• One table change → multiple DB updates
• Views had to be updated everywhere
• Execution order mattered
• Debugging was reactive

👉 This was not a migration issue — it was an architecture issue

Final Conclusion

My final stance:

• Transactional logic → single DB
• Foreign keys → within DB only
• No cross-DB views in core business flow
• Use S2S or events for cross-boundary interaction

👉 Design is about deciding where constraints should live

Conversation History (One-line)

Started with frustration on FKs and views → explored migration pain → identified cross-DB coupling → clarified integrity ownership → redefined boundaries → concluded proper architectural rules

Next Steps

• Pick one real flow and refactor it
• Remove cross-DB view dependency
• Either merge into single DB or replicate data
• Measure migration simplicity after change