Business Software Is Hard

Essay 6

Why does modularity stop at the database?

If information hiding matters in code, why do we so often expose the entire relational representation underneath it?

I have spent a lot of time arguing that a Java package can be a useful module: related implementation behind a small public surface, most classes package-private, and dependencies kept visible.

Then I open the database underneath one of those applications and find almost everything in the default schema: dbo in SQL Server, public in PostgreSQL.

I have worked with systems where hundreds or thousands of tables lived together in one schema. The problem was not tidiness. It was change. If I needed to alter one business concept, the schema gave me little help identifying what belonged to it. I searched the codebase for table names, views, joins and updates and hoped I had found every use. A table that looked internal might be read by a report, updated by a batch job and joined from SQL in another package.

Even modest changes became risky. Splitting a table, changing a key or moving a derived value could mean editing SQL scattered across the application. Finding those statements was work; knowing that I had found all of them was harder. Database-level tests were often much thinner than the class-level unit tests around the Java code, so confidence was weakest where the representation had leaked furthest.

In the first essay I called this kind of mistake a level error: a sound principle is applied at the wrong unit. We can spend enormous effort applying modularity to individual classes and interfaces while missing the larger unit that ought to be able to change independently. In Java that may be a package representing a business area rather than each class inside it.

The same mistake appears at the database connection. We carefully hide implementation details inside application packages, then cross JDBC and expose hundreds of tables as one shared surface. The business system did not stop having structure below the connection; we simply stopped applying the modularity principle there.

If orders is a real business module, its Java package and its database schema can be two representations of the same decomposition. Most order code and persistent representation can then change behind one boundary, while the rest of the system depends on a smaller published surface.

Why does that not happen more often? Partly because the defaults lead elsewhere. SQL Server commonly lands unqualified objects in dbo; PostgreSQL starts with public in the search path. ORMs, migration tools and examples often make unqualified names the path of least resistance. Multi-schema design is not a decision made once; it is a discipline that has to survive tooling that continually makes the one-schema design easier.

The code boundary also has a compiler. A package reference either resolves or it does not. Database dependencies are often discovered later, at migration time or execution time, and an unqualified name may make the dependency less obvious. One of the interesting consequences of making transactions and schemas explicit is that we can begin to build some of that missing feedback ourselves.

The vendors have not forgotten this machinery. Microsoft’s AdventureWorks sample uses business-oriented schemas such as Production, Sales and HumanResources. Oracle systems have long used separate application schemas with explicit grants between them. The idea is not new. What is unusual is treating it as part of ordinary application design.

One module, two representations

By module I mean a business area whose design decisions and persistent facts tend to change together. An application package and a database schema are two mechanisms that can represent that module.

A Java package is not automatically a module, and neither is a schema. Calling four collections of tables orders, inventory, credit and payments does not prove that we found four useful units of change. The names have to correspond to something in the business.

This is Parnas applied below the database connection. We are trying to hide design decisions that should be able to change without forcing unrelated parts of the system to change with them.

Persistent business facts may be durable. Their current physical representation in tables need not be.

The database schema also gives the entity model some structure. A 900-table diagram is close to useless; the same model divided into named business regions is at least navigable. Those regions then give us a surface on which to draw the transaction interactions from Drawing the transaction and Why does it only fail in production?.

Can we test whether the modules are real?

This is the part I find most interesting.

If schemas and packages really represent business modules, named transactions ought to tell us something about the quality of those boundaries.

Suppose we have:

Now inspect every named transaction and count the distinct schemas it touches.

A result such as this would be encouraging:

Schema span Share of transactions
One schema 72%
Two schemas 21%
Three schemas 6%
Four or more 1%

Most business operations remain inside one module. Some cross one boundary. A small number span several areas and deserve examination.

A very different distribution would tell us something too:

Schema span Share of transactions
One schema 10%
Two schemas 25%
Three schemas 35%
Four or more 30%

At that point we may have four namespaces rather than four modules.

The useful thing is that this need not remain architectural taste. If transactions are named and their SQL is available, a static pass can report which schemas each transaction reads and writes. It can identify cross-schema updates, build the distribution, and surface the outliers.

That starts to close the compiler asymmetry. The Java compiler tells us when package dependencies are broken. Transaction tooling can tell us when a supposedly local business operation reaches through several database boundaries.

The outliers are not automatically wrong. A transaction spanning orders and credit may preserve a genuine invariant across both. Repeated cross-schema traffic can mean that the boundary is wrong, the transaction is too broad, or the business itself contains real coupling. The measurement does not make the decision for us; it tells us where to look.

This also connects design to testing. If orders is supposed to be a module, we can ask how many transaction tests cover operations inside it, which transactions cross out of it, and whether changes to its private representation break anything outside its published surface.

What is public?

The boundary becomes useful only if we distinguish interface from representation.

For reads, a module can publish relational projections. A report or another part of the application need not know that orders happens to store headers, lines, approval state and history in a particular set of tables. It can depend on a deliberate surface such as orders.order_details or orders.open_orders.

That is especially valuable for reporting, because reports are often the consumers that accidentally turn internal tables into permanent APIs. A published reporting surface gives them something stable to depend on while leaving the base representation freer to change.

Reporting is not identical to transactional application access, though. A large analytical report may have very different performance, locking and freshness needs from a transaction reading an order before an update. Sometimes the right reporting architecture is a replica, warehouse or separate analytical store. The point is still the same: the reporting contract should be deliberate rather than whatever base tables happened to be convenient.

Views are one mechanism for publishing that contract, but they are not free abstraction. Simple projections are usually easy to reason about; complex stacks of views, aggregates and joins can become performance-sensitive. SQL Server indexed views require SCHEMABINDING, which deliberately restricts changes to underlying objects. The mechanism used to make a projection fast can therefore reduce the freedom the projection was meant to provide.

A view is a contract with evolution and performance costs, not magic indirection.

Relational keys can be contracts too. If credit has a foreign key to orders.order_header, then orders has effectively published that key. The consumer is coupled to its existence and type. That can be an excellent dependency: the database now enforces an important fact. But it should be treated with the same seriousness as any other public interface.

Cascading actions deserve particular care. A cascading delete is a write path crossing a module boundary without going through a named transaction. Sometimes that is exactly the invariant we want. Sometimes it silently defeats the write boundary we thought we had.

For writes, the transaction essays give us a stronger public surface than writable tables. Instead of saying “these six tables are available”, the module can expose named changes such as orders.create_order, orders.amend_order, orders.approve_order and orders.cancel_order.

Conceptually:

Surface Examples Stability
Public reads orders.open_orders, orders.order_details Published relational contract
Public changes orders.create_order, orders.approve_order Named business operations
Private representation base tables, history tables, helper views, indexes Free to change within the module

What about shared things?

This is the first practical problem with any neat modular picture. Where does Currency go?

Putting it in orders is obviously wrong if payments, credit and reporting all need it. Putting every such thing into common, shared or a giant reference module is not much better; that can become dbo with a different name.

The useful question is not “which single module uses this?” but who owns the definition and evolution of this concept?

Currency is a good example of genuinely shared vocabulary. Java already provides java.util.Currency for ISO currencies. If an application needs a richer business-specific currency type, I would still make it a small shared concept, perhaps a currency package, rather than burying it inside whichever module needed it first. Many modules depending on a tiny, stable currency vocabulary is not worrying.

There is an important difference once the shared concept becomes persistent data. A widely referenced Java class creates logical coupling, but a widely referenced database table can also sit on foreign-key paths, participate in locking and contention, and make schema changes coordinate with many consumers. That does not make the boundary wrong. It means that a genuinely shared database concept is also a physical point of coordination, so its published surface should be correspondingly small and stable.

Customers and products are different. They may be read by almost everything, but they usually have lifecycle, rules, permissions and transactions of their own. Widespread use does not make them reference data; it makes them important modules with widely used public surfaces.

Other concepts sit somewhere between the two: legal entities, organisational structures, business calendars, market classifications. They may look like reference data until their rules start changing. Then the “reference” label becomes a way of hiding ownership rather than expressing it.

So I would distinguish:

A good modular architecture is a graph, not a tree. Shared nodes are fine when they are deliberate, small and stable.

Enforcement, migrations and execution context

There is a tension between application-side SQL and database-enforced boundaries.

If one monolithic application connects using one database identity with UPDATE rights on every table, the database cannot stop code in the credit package from updating orders tables directly. The package/schema boundary is still useful, but it is advisory.

Separate database users or module-specific connection pools could enforce it, but that has an obvious operational cost. Twelve modules can become twelve pools, each needing capacity and lifecycle management. I would not introduce that machinery merely to make an architectural diagram look pure.

If the important transactions are named and executed through some common mechanism — whatever begins, commits and retries them — that mechanism suggests a cleaner option. It already knows the identity of the named transaction, so it can also own the database execution context.

For a substantial schema I would distinguish three capabilities:

Ownership, published read access and transaction implementation access are different things. In a simple system the writer role may inherit the reader role, but it should not automatically mean unrestricted ownership of the schema.

The database products provide different mechanisms for applying those rights without creating a connection pool per module. PostgreSQL can use transaction-scoped role switching with SET LOCAL ROLE; SQL Server can use execution contexts such as EXECUTE AS, or signed modules where that is the better fit. The exact mechanism belongs in vendor implementation notes. The design point is that a shared pool can remain shared while a named transaction executes with narrower database authority.

This also separates two kinds of authority that are easy to blur together. The application decides whether the current user may perform orders.approve_order; the database execution context determines what that transaction is allowed to read or change. The named transaction is the natural point where those two concerns meet.

This only becomes an enforced boundary if ordinary application code cannot bypass the transaction mechanism and use a broadly privileged raw connection. Otherwise it remains an architectural convention, which may still be useful but should be described honestly.

Migrations fit the same ownership idea without implying independent deployment. One database can still have one coordinated migration pipeline. The orders module owns changes to the orders schema, and migrations crossing several schemas are visible because they cross module boundaries. We keep one relational database without pretending it has no internal ownership.

This is not microservices inside the database

None of this means that schemas should behave like little remote services.

A query can join orders, customers and payments. A foreign key can cross schemas. A transaction can preserve an invariant spanning orders and credit. Those are advantages of keeping the modules inside one relational system.

The mistake would be to ban joins, replace foreign keys with application calls, duplicate data to avoid crossing boundaries or require every interaction to look like an API request. We would recreate many of the costs of microservices while giving up the strengths of the database.

The question is narrower:

Should every consumer know every internal table?

Usually, no.

A relational module can publish relations, stable keys and named transactions while keeping most of its physical representation private. Cross-module relationships remain possible; they simply become visible design commitments. Unlike a Java package boundary, some of those commitments also create physical coordination through foreign keys, locking, contention and migration dependencies. Database modularity therefore has operational consequences as well as information-hiding benefits.

Oracle is useful prior art here. Oracle applications have long used separate schemas, grants and synonyms to create ownership and access boundaries inside one database. The mechanism differs from SQL Server and PostgreSQL, but the underlying idea is old: one relational database does not require one undifferentiated namespace.

A practical test

Take a database you know well and ignore its current schemas for a moment. Identify a few business areas whose rules and persistent representation tend to change together.

Pick one. Which tables belong to its private physical representation? Which relations, keys or views are deliberately public? Which changes should other parts of the system cause through named transactions rather than direct updates? Which supposedly shared tables are genuinely tiny shared vocabulary, and which are business modules hiding under the label reference?

Then inspect the transactions. Count how many schemas each named transaction reads and writes. Look at the distribution and investigate the outliers. If almost every transaction crosses every proposed boundary, the decomposition is telling you very little.

Finally ask what could be checked automatically. Can tooling report cross-schema reads and writes? Can it flag access to private tables from the wrong application package? Can schema ownership be reflected in migrations and transaction tests? The database may not give us the same compiler boundary as Java, but we do not have to accept having no feedback at all.

The goal is not more schemas.

The goal is to be able to change one business area without first reverse-engineering the whole database.

References

Background

Where the detail lives