Data Design and Database Management
Read at your own pace. Code execution, quizzes, progress tracking, and the AI tutor are available in the interactive view with JavaScript enabled.
Why Data Design
From services to state
In Microservices Architecture, we split behavior into independently deployable services. But every useful system also remembers things: users, orders, payments, inventory, events.
That remembered state is usually harder to change than code.
In this lesson, we will design data like architects:
- Turn requirements into entities and relationships
- Choose between relational and document-style storage
- Use indexes intentionally
- Protect correctness with transactions and constraints
- Manage database change safely with migrations
Think of the database as the system’s long-term memory. Bad memory design makes even clean Java and Spring Boot code painful.
Model The Nouns
Start with business language
From High-Level System Design, you already know requirements drive the blueprint. For data design, listen for nouns and rules.
Example requirement:
A customer can place many orders. Each order contains one or more items. Each item references a product snapshot and quantity.
Possible model:
Customer 1 ──── * Order 1 ──── * OrderItem
|
* stores product name and price at purchase time
Notice the phrase product snapshot. We usually do not want an old order total to change just because the product price changes tomorrow.
Good data design asks:
- What must be remembered exactly?
- What can be recomputed?
- What changes often?
- What must never become inconsistent?
Domain Before Tables
Let Java reveal the rules
Before creating tables, model the domain rules in Java. This connects directly to Low-Level Design Principles: keep invariants close to the object that owns them.
Here, an Order refuses to exist without items, and it calculates its own total. Later, this can map to relational tables, document storage, or API responses.
```java
import java.math.BigDecimal;
import java.util.List;
public class Main {
record OrderItem(String productName, int quantity, BigDecimal unitPrice) {
OrderItem {
if (productName == null || productName.isBlank()) {
throw new IllegalArgumentException("Product name is required");
}
if (quantity <= 0) {
throw new IllegalArgumentException("Quantity must be positive");
}
if (unitPrice.compareTo(BigDecimal.ZERO) < 0) {
throw new IllegalArgumentException("Price cannot be negative");
}
}
BigDecimal subtotal() {
return unitPrice.multiply(BigDecimal.valueOf(quantity));
}
}
static class Order {
private final String customerId;
private final List<OrderItem> items;
Order(String customerId, List<OrderItem> items) {
if (customerId == null || customerId.isBlank()) {
throw new IllegalArgumentException("Customer is required");
}
if (items == null || items.isEmpty()) {
throw new IllegalArgumentException("Order must contain at least one item");
}
this.customerId = customerId;
this.items = List.copyOf(items);
}
BigDecimal total() {
return items.stream()
.map(OrderItem::subtotal)
.reduce(BigDecimal.ZERO, BigDecimal::add);
}
}
public static void main(String[] args) {
Order order = new Order("customer-101", List.of(
new OrderItem("Keyboard", 2, new BigDecimal("49.99")),
new OrderItem("Mouse", 1, new BigDecimal("19.99"))
));
System.out.println("Order total: $" + order.total());
}
}
```Pick The Store
You are designing an order system where you need strong consistency for payments, frequent joins between customers and orders, and clear constraints like order_total >= 0.
Which storage choice is usually the best starting point?
Relational Shape
Tables are promises
A relational schema is not just storage. It is a set of promises about valid data.
Example order schema:
customers
- id PK
- email UNIQUE
- name
orders
- id PK
- customer_id FK -> customers.id
- status
- created_at
order_items
- id PK
- order_id FK -> orders.id
- product_name_snapshot
- unit_price_snapshot
- quantity
Important design ideas:
- Primary key: stable identity for one row
- Foreign key: relationship enforced by the database
- Unique constraint: prevents duplicates, like duplicate email accounts
- Check constraint: prevents impossible values, like negative quantity
- Normalization: avoid repeated facts that can drift apart
But normalization is not religion. Sometimes you intentionally duplicate read-friendly data, such as product name at purchase time, because history matters.
Indexes Are Maps
Speed has a cost
An index is like the back-of-book index in a textbook. Instead of scanning every row, the database uses a structure to jump near the answer.
Useful indexes often come from query patterns:
-- Query pattern
SELECT * FROM orders WHERE customer_id = ? ORDER BY created_at DESC;
-- Helpful index
CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at DESC);
But indexes are not free:
- They consume disk and memory
- They slow down inserts and updates
- Too many indexes confuse maintenance and tuning
Design rule: index the questions your application actually asks.
```java
import java.util.*;
import java.util.stream.Collectors;
public class Main {
record Order(String id, String customerId, long createdAtEpochSecond) {}
static class OrderRepository {
private final List<Order> table = new ArrayList<>();
private final Map<String, List<Order>> indexByCustomer = new HashMap<>();
void save(Order order) {
table.add(order);
indexByCustomer
.computeIfAbsent(order.customerId(), ignored -> new ArrayList<>())
.add(order);
}
List<Order> slowFindByCustomer(String customerId) {
return table.stream()
.filter(order -> order.customerId().equals(customerId))
.collect(Collectors.toList());
}
List<Order> fastFindByCustomer(String customerId) {
return indexByCustomer.getOrDefault(customerId, List.of());
}
}
public static void main(String[] args) {
OrderRepository repo = new OrderRepository();
repo.save(new Order("order-1", "customer-1", 100));
repo.save(new Order("order-2", "customer-2", 110));
repo.save(new Order("order-3", "customer-1", 120));
System.out.println("Slow scan result: " + repo.slowFindByCustomer("customer-1"));
System.out.println("Indexed result: " + repo.fastFindByCustomer("customer-1"));
}
}
```Database Ownership
True or false:
In a microservices architecture, it is usually a good idea for one service to directly read and write another service’s database tables because it is faster than calling an API.
Transactions Protect Meaning
Correctness beats cleverness
A transaction groups multiple changes into one unit of work.
Classic example: transferring money.
Both must happen:
- Debit account A
- Credit account B
If step 1 succeeds but step 2 fails, the system has invented a bug called missing money.
Databases use ACID properties to control this:
- Atomicity: all or nothing
- Consistency: rules remain valid
- Isolation: concurrent work does not corrupt meaning
- Durability: committed data survives failure
In Spring Boot, this is why @Transactional is not decoration — it is a correctness boundary.
```java
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.Map;
public class Main {
static class Bank {
private final Map<String, BigDecimal> balances = new HashMap<>();
void open(String accountId, BigDecimal amount) {
balances.put(accountId, amount);
}
void transfer(String from, String to, BigDecimal amount, boolean simulateFailure) {
Map<String, BigDecimal> snapshot = new HashMap<>(balances); // simple rollback simulation
try {
balances.put(from, balances.get(from).subtract(amount));
if (simulateFailure) {
throw new RuntimeException("Network error before crediting receiver");
}
balances.put(to, balances.get(to).add(amount));
System.out.println("Transfer committed");
} catch (RuntimeException ex) {
balances.clear();
balances.putAll(snapshot);
System.out.println("Transfer rolled back: " + ex.getMessage());
}
}
void printBalances() {
System.out.println(balances);
}
}
public static void main(String[] args) {
Bank bank = new Bank();
bank.open("alice", new BigDecimal("100.00"));
bank.open("bob", new BigDecimal("50.00"));
bank.transfer("alice", "bob", new BigDecimal("25.00"), true);
bank.printBalances();
bank.transfer("alice", "bob", new BigDecimal("25.00"), false);
bank.printBalances();
}
}
```Find The Word
Fill in the blank:
A database ______ is a data structure that helps the database find rows faster for specific query patterns, often at the cost of slower writes and extra storage.
Change Data Safely
Databases need version control too
Code changes constantly. Data schemas change more carefully.
A database migration is a controlled step such as:
ALTER TABLE orders ADD COLUMN status VARCHAR(30) NOT NULL DEFAULT 'CREATED';
A safe migration mindset:
- Make changes small and reversible when possible
- Add columns before code depends on them
- Backfill old data intentionally
- Avoid long locks on large tables
- Keep migration files in source control
- Run the same migration path in dev, test, and production
Tools like Flyway and Liquibase are common in Java/Spring Boot systems.
Next, when we discuss Cloud-based Architecture, you will see how deployment environments affect database availability, backups, and scaling choices.
