Designing Relational Data Models with ACID Transactions

Relational databases remain one of the safest choices when an application needs structured data, clear relationships, and strong transaction guarantees.

That does not mean every data problem should start with a relational schema. It means that when your data has a stable shape, meaningful relationships, and correctness requirements, a relational database gives you a proven model for storing and protecting that data.

For Java developers, relational databases often appear through JPA, repositories, SQL queries, and database-backed services. The architectural decision is bigger than choosing a driver. You are deciding where your consistency boundary lives and how much of the domain should be shaped by the database.

The Problem

A common mistake is to treat the database as the entire design.

A team starts with tables, adds columns, creates relationships, and then builds Java classes that mirror the schema. This can work for simple CRUD applications, but it becomes limiting when the domain contains behavior, workflows, and rules that do not fit neatly into rows and columns.

Weak design flow:
Database tables
  |
  v
Java data objects
  |
  v
Service methods with most business logic

A better approach is to model the business first, then choose a persistence model that supports it.

Better design flow:
Domain concepts
  |
  v
Consistency and relationship needs
  |
  v
Relational schema
  |
  v
Repository or persistence layer

The relational database is not the domain model. It is a storage technology that can support the domain model when used deliberately.

Core Idea

A relational database stores data in tables. Each table can roughly map to a business object. Columns map to fields, and rows represent object instances.

That mapping is useful, but it is not perfect. Object-oriented code has inheritance, interfaces, encapsulation, and behavior. Tables do not express all of that directly.

This is why Java applications commonly use a persistence layer. JPA can map Java entities to database tables, but the application should still preserve a clean boundary between domain behavior and storage details.

A simple relationship view can look like this:

User
  |
  | one-to-many
  v
Payment

Payment
  |
  | many-to-one
  v
Recipient

The database helps protect structure. The application still needs to protect meaning.

Keys and Constraints

The first important relational concept is the primary key.

A primary key identifies one row uniquely. It may be one field or a combination of fields. From an architecture perspective, the important point is that it defines identity inside the table.

Relational databases can also enforce constraints. A constraint is a rule that stored data must follow. A primary key is one example because it prevents duplicate identities. Other constraints may validate required values or relationships.

A simple schema sketch might look like this:

payments
  payment_id        unique identifier
  sender_id         related user
  recipient_id      related user
  amount            payment amount
  status            current payment state

Do not confuse a database primary key with every possible business identity. Sometimes they are the same. Sometimes the database uses an internal identifier while the business uses another identifier, such as an account number or transaction reference.

Relationship Cardinality

Relational databases model links between tables with relationships. The common cardinalities are one-to-one, one-to-many, and many-to-many.

One-to-one means one row points to exactly one row in another table. A user profile and a separate address row can be modeled this way when there is only one address record for each profile.

One-to-many means one row in a source table relates to many rows in another table. A user with many payment transactions is a classic example.

Many-to-many means multiple rows on both sides can connect to each other. In relational modeling, this is usually represented with a joining table.

One-to-one:
person -> address

One-to-many:
user -> payment transactions

Many-to-many:
person -> device
       via person_device

These relationships are powerful, but they also create query complexity. A model that looks clean on paper may require expensive joins when the data grows.

Transactionality and ACID

Relational databases are especially strong when transactionality matters.

A transaction groups several operations into one logical unit. The database applies the full unit or rejects the full unit. This protects the application from partial writes that leave the data in a broken state.

ACID describes the core properties:

For a payment system, this matters. If a payment operation updates balances, stores a transaction record, and changes status, partial completion can be dangerous.

A conceptual transaction boundary can be described like this:

Begin payment transaction
  validate database constraints
  store payment record
  update status
  commit everything together
If any required step fails
  rollback everything

Relational databases are a strong fit when the business cannot tolerate inconsistent writes.

Stored Procedures

Many relational databases allow custom code to run directly inside the database through stored procedures.

The main benefit is data locality. Code running in the database can operate close to the data and may gain performance for large batch operations or calculations. The database also controls locking and transactional behavior.

The architectural downside is significant. Business logic inside the database is harder to test with the same tools as application code, harder to version with the rest of the system, and often tied to one database vendor.

Stored procedure tradeoff:
Better data locality
  +
Possible performance gains
  -
Business logic moves into the data layer
  -
Portability becomes harder
  -
Maintenance becomes more complex

Use stored procedures only when the performance or operational reason is strong enough to justify the coupling.

Implementation Choices

The chapter highlights several common relational database implementations:

• Oracle, widely used in enterprise environments.
• Microsoft SQL Server, common in the Microsoft ecosystem and also available beyond Windows.
• MySQL, a long-standing open-source database with MariaDB as an important fork.
• PostgreSQL, a mature open-source database with strong feature coverage.
• H2, a Java database often useful for development, testing, and demo scenarios.
• SQLite, an embeddable database implemented in C.

The practical point is not to memorize product names. The point is to choose based on the use case. Production-grade transactional systems, test pipelines, embedded demos, and enterprise packaged software may all need different database choices.

Practical Workflow

1. Start with the domain model, not with the tables.
2. Identify the data that has stable structure.
3. Identify the data that needs strong consistency.
4. Define primary keys and business identifiers deliberately.
5. Model relationships only where they add clear meaning.
6. Keep transaction boundaries small and explicit.
7. Use repositories or persistence adapters to hide database details.
8. Avoid putting core business rules in stored procedures unless there is a strong reason.
9. Review complex queries with database specialists before production.
10. Monitor query behavior and index health after release.

Common Mistakes

The first mistake is designing every Java object from the database schema. This creates a storage-shaped application instead of a domain-shaped application.

The second mistake is using relational databases where the data has no stable structure and no strong transaction requirements. In that case, the team may pay for rigidity without getting enough value from it.

The third mistake is hiding important business behavior in stored procedures. That can make the system difficult to test, move, and maintain.

The fourth mistake is ignoring query complexity. Many joins, large tables, and poor index usage can damage performance even when the model looks correct.

The fifth mistake is assuming ACID transactions work naturally across a heavily distributed architecture. Distributed transaction design needs special care and often requires different patterns.

Checklist

• The data structure is known and stable enough for tables.
• Primary keys are explicit.
• Business identifiers and technical identifiers are not confused.
• Relationship cardinality is understood.
• ACID behavior is required and valuable.
• Transaction boundaries are clear.
• Business logic mostly stays outside the database.
• Complex queries are reviewed before production.
• Database choice matches production, testing, or embedded needs.
• The persistence layer does not leak everywhere in the domain.

Conclusion

Relational databases are still a strong foundation for Java systems that need structured data and reliable transactions.

Use them when the model is stable, relationships matter, and ACID guarantees protect important business operations. Keep the domain model clean, keep transaction boundaries intentional, and avoid turning the database into a hidden application layer.