Database Design

From fast code to durable systems

Now that we've covered Intermediate Java and Data Structures and Algorithms, you can reason about in-memory structure: lists, sets, maps, traversal, and growth.

Database design asks a related but different question:

How should data be shaped so it remains correct, queryable, and change-friendly for years?

For interviews and real engineering, this is where your code meets persistence. A great Java service can still become painful if the database model is vague, duplicated, or missing constraints.

In this lesson, we'll focus on practical relational design:

• entities and relationships
• primary and foreign keys
• normalization and intentional denormalization
• indexes as read/write tradeoffs
• constraints and transactions
• a review checklist you can use in interviews

We'll stay scoped to database design. Later, Software Architecture and System Design will zoom out to service boundaries and large-scale distributed behavior.

Your object model is not automatically your database model

In Java, you often start with objects: User, Order, Product. In a relational database, you model durable facts as tables, rows, and relationships.

A useful mental shift:

• A Java object is usually a runtime behavior container.
• A database row is a persistent fact.
• A table is a set of similarly shaped facts.
• A relationship is represented with keys, not object references.

For example, an order containing multiple products should usually not store a comma-separated product list. Instead, model the relationship explicitly with an order_items table.

This keeps queries simple, avoids parsing strings, and lets the database enforce meaning.

```java
import java.util.*;

public class Main {
    record Order(long id, long userId) {}
    record OrderItem(long orderId, long productId, int quantity) {}

    public static void main(String[] args) {
        Order order = new Order(101, 7);
        List<OrderItem> items = List.of(
            new OrderItem(order.id(), 501, 2),
            new OrderItem(order.id(), 777, 1)
        );

        System.out.println("orders table row:");
        System.out.println("id=" + order.id() + ", user_id=" + order.userId());

        System.out.println("\norder_items table rows:");
        for (OrderItem item : items) {
            System.out.println("order_id=" + item.orderId()
                + ", product_id=" + item.productId()
                + ", quantity=" + item.quantity());
        }
    }
}
```

You are designing an online store. Each order can contain many products, and each product can appear in many orders.

Which relational model is usually the best starting point?

Keys give rows identity and meaning

A table should have a clear way to identify each row.

Primary key

A primary key uniquely identifies a row in its own table.

Example:

users(id, email, name)

Here, id might be the primary key.

Foreign key

A foreign key points from one table to another table's primary key.

Example:

orders(id, user_id, created_at)

Here, orders.user_id references users.id.

Why this matters

Foreign keys protect your data from impossible states, like an order belonging to a user that does not exist.

That is a database-level guarantee. It does not depend on every Java caller remembering to validate correctly.

Normalization reduces update pain

Normalization is the practice of organizing data so each fact has a clear home.

You do not need to recite every normal form to use the idea well. Ask:

If this fact changes, how many places must be updated?

Bad sign:

orders(id, user_email, user_name, product_name, product_price)

This duplicates user and product facts across many orders.

Better starting point:

users(id, email, name)
products(id, name, current_price)
orders(id, user_id, created_at)
order_items(order_id, product_id, quantity, price_at_purchase)

Notice the nuance: current_price belongs on products, but price_at_purchase belongs on order_items because it is a historical fact about that order.

Good design is not blindly minimizing columns. It is putting each fact where it remains true.

A column in one table that references the primary key of another table is called a ________.

Indexes speed up lookups, but they are not free

From Data Structures and Algorithms, you already know access shape matters. A database index is similar in spirit: it helps the database find rows without scanning everything.

Common index candidates:

• columns used in WHERE
• columns used in joins
• columns used for sorting or range queries
• foreign keys in frequently queried relationships

But indexes have costs:

• extra storage
• slower writes because the index must be maintained
• possible misuse if added without understanding query patterns

Think of an index as a read optimization you earn by knowing how the data is accessed.

```java
import java.util.*;

public class Main {
    record User(long id, String email) {}

    public static void main(String[] args) {
        List<User> users = List.of(
            new User(1, "ana@example.com"),
            new User(2, "ben@example.com"),
            new User(3, "cora@example.com")
        );

        String target = "cora@example.com";

        User foundByScan = null;
        int scanChecks = 0;
        for (User user : users) {
            scanChecks++;
            if (user.email().equals(target)) {
                foundByScan = user;
                break;
            }
        }

        Map<String, User> emailIndex = new HashMap<>();
        for (User user : users) {
            emailIndex.put(user.email(), user);
        }
        User foundByIndex = emailIndex.get(target);

        System.out.println("Scan checks: " + scanChecks + ", found id=" + foundByScan.id());
        System.out.println("Index lookup found id=" + foundByIndex.id());
        System.out.println("Tradeoff: faster reads, extra structure to maintain.");
    }
}
```

True or false: A well-designed database should add an index to every column so every possible query is fast.

A transaction groups changes into one promise

Some database operations only make sense if all parts succeed together.

Classic example: transferring money.

• subtract from account A
• add to account B

If the first update succeeds and the second fails, your data becomes incorrect. A transaction says:

Commit all changes, or roll them all back.

This matters for database design because tables, constraints, and transaction boundaries work together. Your schema should make invalid states hard to represent, and transactions should protect multi-row changes that must stay consistent.

```java
import java.util.*;

public class Main {
    static class AccountStore {
        private final Map<String, Integer> balances = new HashMap<>();

        AccountStore() {
            balances.put("A", 100);
            balances.put("B", 50);
        }

        void transfer(String from, String to, int amount, boolean simulateFailure) {
            Map<String, Integer> snapshot = new HashMap<>(balances);
            try {
                balances.put(from, balances.get(from) - amount);

                if (simulateFailure) {
                    throw new RuntimeException("network timeout before credit");
                }

                balances.put(to, balances.get(to) + amount);
                System.out.println("COMMIT");
            } catch (RuntimeException ex) {
                balances.clear();
                balances.putAll(snapshot);
                System.out.println("ROLLBACK: " + ex.getMessage());
            }
        }

        void printBalances() {
            System.out.println("A=" + balances.get("A") + ", B=" + balances.get("B"));
        }
    }

    public static void main(String[] args) {
        AccountStore store = new AccountStore();
        store.printBalances();
        store.transfer("A", "B", 30, true);
        store.printBalances();
        store.transfer("A", "B", 30, false);
        store.printBalances();
    }
}
```

A practical database design pass

When evaluating a schema in an interview or code review, walk through this checklist.

1. Entities

What durable things does the system need to remember?

Examples: users, orders, products, payments.

2. Relationships

Are relationships one-to-one, one-to-many, or many-to-many?

Many-to-many usually needs a join table.

3. Keys

Does every table have a primary key? Are foreign keys explicit where integrity matters?

4. Facts

Does each fact live in the right place? If it changes, how many rows must be updated?

5. Queries

What are the most important reads? Which columns filter, join, sort, or group data?

6. Constraints

Can the database reject impossible data using NOT NULL, UNIQUE, CHECK, and foreign keys?

7. Transactions

Which operations must succeed or fail as a unit?

A strong engineer does not just ask, "Can I store this?" They ask, "Will this still be correct and understandable after the product evolves?"