Database Design Best Practices Every Backend Dev Should Know

A bad API can be versioned. A bad database schema haunts you forever.

I’ve learned this the hard way: the database is the foundation of everything. I can refactor code, version my APIs, rebuild the frontend — but a poorly designed database will slowly poison everything that touches it. Migrations are risky, data is hard to move, and by the time I realize the schema is wrong, half my codebase has grown around its shape.

These are the principles and practices I keep coming back to.

1. Start With Normalization

I treat normalization like drawing the floor plan before I pick paint — if the walls are in the wrong place, no amount of indexing fixes the layout.

Normalization — getting to a sane shape before I tune anything else — lives in a separate post: DB Design: The Three Normal Forms. That walkthrough covers 1NF through 3NF, partial vs transitive dependencies, a four-step decomposition framework for messy tables, and when I deliberately denormalize. I read the two posts as a pair: shape the data first, then apply what follows.

Normalize until it hurts, denormalize until it works. I start from a clean normalized baseline and only denormalize when reads or reporting need it — never by accident.

graph LR
    M["Messy table (duplicated facts)"] --> N["Normalize (1NF-3NF)"]
    N --> B["Baseline schema"]
    B --> D["Denormalize on purpose (when needed)"]

    classDef mess fill:#fff,stroke:#c62828,stroke-width:2px,color:#000;
    classDef step fill:#fff,stroke:#0277bd,stroke-width:2px,color:#000;
    classDef done fill:#fff,stroke:#2e7d32,stroke-width:2px,color:#000;
    class M mess;
    class N,B step;
    class D done;

---

2. Logical Foreign Keys Over Physical Foreign Keys

This one tripped me up when I was learning database design from textbooks.

The analogy: I picture an order with a customer_id that should reference a row in customers. The useful question is: when I save that order, does the database stop me if the customer doesn’t exist?

• If yes — physical foreign key. That’s the bouncer with a list in the engine: on insert or update, it checks the id against customers. No match ⇒ the write fails, for my app, a script, or ad-hoc SQL — same rule for everyone.

• If no — logical foreign key. The customer_id column is still there and I still mean “this order belongs to that customer,” but the database does not run that check — it only stores the number, even if the customer row is missing or lives in another system. Same column; different gatekeeper.

A physical foreign key is a FOREIGN KEY constraint declared in the DDL. The database enforces referential integrity — if I try to insert an order with a customer_id that doesn’t exist in the customers table, the database rejects it.

CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total       DECIMAL(10,2),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
);

A logical foreign key is the same column — customer_id referencing a customer — but without the database-level constraint. The relationship exists in my application code and documentation, not in the DDL.

CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total       DECIMAL(10,2)
);

flowchart LR
    subgraph p ["Physical FK (same database)"]
        direction LR
        O1[orders] -->|FOREIGN KEY<br/>DDL enforced| C1[(customers)]
    end
    subgraph l ["Logical FK (split systems)"]
        direction LR
        O2[orders.customer_id] -.->|no DDL link| C2[(customers<br/>here or elsewhere)]
    end

    classDef box fill:#fff,stroke:#0277bd,stroke-width:2px,color:#000;
    classDef tbl fill:#fff,stroke:#2e7d32,stroke-width:2px,color:#000;
    class O1,O2 box;
    class C1,C2 tbl;

Why would I skip the constraint (the physical FOREIGN KEY in DDL)? It felt dangerous to me at first. But in practice, many production systems at scale intentionally use logical foreign keys:

• Cross-service boundaries. In a microservices architecture, orders and customers might live in different databases entirely. I can’t declare a foreign key across databases. The customer_id in the orders DB is a logical reference — my code is responsible for ensuring it’s valid.

• Schema migration pain. Foreign keys make ALTER TABLE operations slower and riskier on large tables. Dropping and recreating constraints during a migration on a table with hundreds of millions of rows can lock the table for minutes. Many teams at scale (Shopify, GitHub, Meta) have documented easing off physical foreign keys in parts of their stack or for certain high-churn tables — not necessarily everywhere.

• Insert ordering. Foreign keys enforce insert order — I must insert the parent before the child. In bulk imports or event-driven systems, records may arrive out of order. Logical foreign keys give me the flexibility to insert records in any order and reconcile later.

This doesn’t mean “never use physical foreign keys.” For a monolithic application with a single database and strong data integrity requirements (financial systems, for example), physical foreign keys are valuable. The point is: understand the tradeoff. Physical foreign keys give me database-enforced integrity. Logical foreign keys give me operational flexibility. I pick the one that matches my architecture.

// With logical foreign keys, my application enforces integrity
public Order createOrder(final CreateOrderRequest request) {
    final Customer customer = customerRepo.findById(request.getCustomerId())
        .orElseThrow(() -> new EntityNotFoundException(
            "Customer not found: " + request.getCustomerId()));

    final Order order = Order.builder()
        .customerId(customer.getId())
        .total(request.getTotal())
        .build();
    return orderRepo.save(order);
}

My rule: use physical foreign keys when I have a single database with strict integrity requirements. Use logical foreign keys when crossing service boundaries, working at scale, or when operational flexibility matters more than database-level enforcement.

---

3. Taming the N+1 Query Problem

I covered the N+1 problem from an API perspective in a previous post. Here I want to zoom into the ORM layer — specifically JPA/Hibernate — because that’s where this problem most often hides in my experience.

The core issue: my ORM loads related entities lazily by default. That means fetching a list of orders doesn’t load their customers until I access each one. In a loop, that means N extra queries.

The analogy: I ask the kitchen for ten plates of food (one query), then ring the supplier ten separate times to ask who grew each tomato. One trip with a shopping list would have done.

sequenceDiagram
    participant App as Application
    participant DB as Database
    App->>DB: SELECT orders (1 query)
    DB-->>App: N rows
    loop Once per order
        App->>DB: SELECT customer by id
        DB-->>App: 1 row
    end

@Entity
public class Order {
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id")
    private Customer customer;
}

List<Order> orders = orderRepo.findAll();
for (Order order : orders) {
    System.out.println(order.getCustomer().getName()); // fires a SELECT per order
}

This silently generates 1 + N queries. Ten orders? Eleven queries. A thousand orders? A thousand and one queries hammering your database.

Fix 1: JOIN FETCH in JPQL

The most direct solution — tell JPA to fetch the related entity in the same query:

@Query("SELECT o FROM Order o JOIN FETCH o.customer")
List<Order> findAllWithCustomers();

This generates a single SELECT ... FROM orders JOIN customers ... instead of 1 + N queries.

sequenceDiagram
    participant App as Application
    participant DB as Database
    App->>DB: SELECT orders JOIN customers (1 query)
    DB-->>App: N rows with customer data

Fix 2: @EntityGraph

If I prefer annotations over query strings:

@EntityGraph(attributePaths = {"customer"})
List<Order> findAll();

Same result — one query with a JOIN — but declared on the repository method rather than in JPQL.

Fix 3: Batch Fetching

Hibernate can batch lazy loads. Instead of N individual SELECT queries, it issues SELECT ... WHERE id IN (?, ?, ?, ...) in chunks:

@Entity
@BatchSize(size = 50)
public class Customer {
    // ...
}

This won’t eliminate the extra queries entirely, but it reduces N queries to roughly N/50. It’s a pragmatic middle ground when I can’t easily rewrite all my queries.

The rule is the same as before: if I’m reading a list of entities and accessing their relationships in a loop, I probably have an N+1 problem. I profile my queries — Hibernate’s hibernate.show_sql=true makes it painfully obvious.

---

4. Optimistic vs Pessimistic Locking

Once more than one request can touch the same row, schema shape isn’t enough — I need a strategy for concurrent updates. Two people read a balance, both add money, both write: without care, one update can silently disappear (lost update). The usual answers are optimistic and pessimistic locking.

The analogy: pessimistic is reserving a hotel room — nobody else can book it until I’m done. Optimistic is two people grabbing the last sale item — whoever gets to checkout first wins; the other has to put it back and decide what to do next.

sequenceDiagram
    participant A as Request A
    participant B as Request B
    participant Row as accounts row
    A->>Row: READ 100
    B->>Row: READ 100
    A->>Row: WRITE 150
    B->>Row: WRITE 120
    Note over Row: Last write wins - one update lost

Pessimistic locking — “lock first, then change”

I take a lock on the row before I read the data I intend to write, and hold it until my transaction ends. In SQL, that’s often SELECT … FOR UPDATE (or the ORM equivalent). Nobody else can modify that row until I commit or roll back.

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
-- read, compute new balance, write
UPDATE accounts SET balance = ? WHERE id = 42;
COMMIT;

When I reach for it: high contention on a small set of rows — think seat maps, inventory for a hot SKU, or a row everyone updates in a short window. The database serializes access; correctness is straightforward.

What I watch for:

• Blocking. Anyone else who needs the same row waits until I commit or roll back. I keep that window small — no slow HTTP calls, file I/O, or “wait for user” logic inside the transaction.

• Deadlocks. If I lock several rows in one transaction, I grab them in a fixed order everywhere (e.g. sort by id before locking). Different orderings across code paths are a classic deadlock recipe.

  Different lock order, same two rows — each session holds one lock and waits on the other:

    sequenceDiagram
        participant A as Session A
        participant B as Session B
        participant R5 as Row 5
        participant R9 as Row 9
        Note over A,B: Opposite lock order — A locks 5 then 9, B locks 9 then 5
        A->>R5: FOR UPDATE (holds)
        B->>R9: FOR UPDATE (holds)
        A->>R9: FOR UPDATE (blocked)
        B->>R5: FOR UPDATE (blocked)
        Note over A,B: Circular wait — neither can finish
  

  Example — two accounts (ids 5 and 9), two transfers in flight:

    -- Bad: Session A (5→9) locks 5 then 9. Session B (9→5) locks 9 then 5.
    -- Interleaved: A holds 5, B holds 9; A waits for 9, B waits for 5 → deadlock.
  
    -- Good: same two rows, same lock order every time (lower id first), any transfer direction.
    BEGIN;
    SELECT * FROM accounts WHERE id = 5 FOR UPDATE;
    SELECT * FROM accounts WHERE id = 9 FOR UPDATE;
    -- … debit / credit …
    COMMIT;
  

  So even for a 9→5 transfer, I still lock 5, then 9 — business direction doesn’t change lock order.

  Fixed lock order — how the deadlock goes away: both sessions queue on the same first row. One waits in line, then both finish — no circle.

    sequenceDiagram
        participant A as Session A
        participant B as Session B
        participant R5 as Row 5
        participant R9 as Row 9
        Note over A,B: Same order every path — 5 then 9 (9→5 transfer still locks 5 first)
        A->>R5: FOR UPDATE (holds)
        B->>R5: FOR UPDATE (waits — A holds 5)
        A->>R9: FOR UPDATE (holds)
        Note over A: COMMIT — releases 5 and 9
        B->>R5: FOR UPDATE (granted)
        B->>R9: FOR UPDATE (holds)
        Note over B: COMMIT
  

• Queueing at the lock. Long work while holding a lock = long queues and timeouts. I do heavy computation before opening the transaction or after commit when I can.

• How I lock one row. My SELECT … FOR UPDATE uses a selective predicate — usually the primary key — so the engine locks exactly the row I mean, not a wide scan.

• Timeouts. Where the engine supports it (PostgreSQL has lock_timeout; others have equivalents), I cap wait time so a blocked session fails fast instead of hanging forever.

graph LR
    T1[Transaction 1] --> L1["FOR UPDATE (holds lock)"]
    T2[Transaction 2] --> W["waits"]
    L1 --> R["Commit / release"]

    classDef lock fill:#fff,stroke:#0277bd,stroke-width:2px,color:#000;
    classDef wait fill:#fff,stroke:#f57c00,stroke-width:2px,color:#000;
    class T1,L1,R lock;
    class T2,W wait;

Optimistic locking — “assume nobody else touched it; verify on write”

I don’t lock on read. Instead I record what version of the row I read — a dedicated version (integer) or sometimes a timestamp — and my UPDATE succeeds only if that version is still current:

UPDATE accounts
SET balance = ?, version = version + 1
WHERE id = 42 AND version = 7;

If zero rows are updated, someone else changed the row first. I retry (re-read, merge, write again) or return a conflict to the client.

sequenceDiagram
    participant Me as My transaction
    participant Other as Other transaction
    participant Row as accounts row id 42
    Me->>Row: READ balance, version 7
    Other->>Row: READ balance, version 7
    Other->>Row: UPDATE ... WHERE version = 7
    Row-->>Other: 1 row updated, version 8
    Me->>Row: UPDATE ... WHERE version = 7
    Row-->>Me: 0 rows, stale version
    Me->>Row: Re-read, retry or abort

When I reach for it: low contention and many readers — most web CRUD. No long-held locks, better throughput when collisions are rare.

What I watch for:

• Idempotent retries. If I re-read and retry after a lost version race, that retry must be safe to run more than once for the same user intent — or I need idempotency keys / conditional writes so I don’t double-apply side effects (charges, emails, duplicate rows).

• A clear conflict contract. When I surface failure to the client, I use something predictable (often HTTP 409) and a message that says why — stale version, concurrent edit — so the UI can refresh or merge instead of showing a vague 500.

• Hot-row thrash. If everyone writes the same row and optimistic locking keeps failing and retrying in a loop, I’m burning latency for little progress. That’s my cue to try pessimistic locking on that hotspot or serialize the work (e.g. a queue) instead of optimistic retry storms.

In JPA, a @Version field on the entity gives me optimistic locking for free — Hibernate increments the version and throws OptimisticLockException when the update loses the race.

@Entity
public class Account {
    @Id private Long id;
    private BigDecimal balance;
    @Version
    private Long version;
}

graph TD
    Q{How hot is<br/>this row?}
    Q -->|Rare conflicts| O[<b>Optimistic</b><br/>version / retry / 409]
    Q -->|Everyone piles on| P[<b>Pessimistic</b><br/>FOR UPDATE / short txn]

    classDef opt fill:#fff,stroke:#2e7d32,stroke-width:2px,color:#000;
    classDef pes fill:#fff,stroke:#0277bd,stroke-width:2px,color:#000;
    classDef dec fill:#fff,stroke:#f57c00,stroke-width:2px,color:#000;
    class O opt;
    class P pes;
    class Q dec;

My rule of thumb: default to optimistic for typical app workloads; switch to pessimistic when I can name the hot rows and correctness under contention matters more than raw parallel throughput. Wrong choice shows up as subtle data bugs or deadlocks, not just slow queries.

---

5. More Practices Worth Adopting

Index What You Query

An index on a column I never filter or sort by is wasted space and slows down writes. An absent index on a column in my WHERE clause turns a millisecond lookup into a full table scan.

I already use the book index mental model below; the other image I keep in mind is a library card catalog — I don’t reread every book spine when I only need authors whose last name starts with M.

graph LR
    Q[<b>Query</b><br/>WHERE customer_id = ?] --> I{Index on<br/>customer_id?}
    I -->|Yes| F[<b>Seek</b><br/><i>few pages</i>]
    I -->|No| S[<b>Full scan</b><br/><i>every row</i>]

    classDef good fill:#fff,stroke:#2e7d32,stroke-width:2px,color:#000;
    classDef bad fill:#fff,stroke:#c62828,stroke-width:2px,color:#000;
    classDef mid fill:#fff,stroke:#f57c00,stroke-width:2px,color:#000;
    class F good;
    class S bad;
    class Q,I mid;

-- If you frequently query orders by customer and status:
CREATE INDEX idx_orders_customer_status
    ON orders (customer_id, status);

The mental model: indexes are like a book’s index. I wouldn’t put every word in the index — just the ones readers actually look up. I profile my slow queries, check my WHERE clauses, and index accordingly.

Soft Deletes Over Hard Deletes

The analogy: a hard delete is shredding a file; a soft delete is moving it to the trash — the bytes are still there until I empty the bin on purpose.

graph TD
    H[<b>Hard DELETE</b>] --> HG[<i>Row gone</i><br/>references may dangle]
    S[<b>Soft delete</b>] --> SF[Set deleted_at]
    SF --> SQ[<i>Same row, filtered out</i><br/>of normal queries]

    classDef warn fill:#fff,stroke:#c62828,stroke-width:2px,color:#000;
    classDef ok fill:#fff,stroke:#2e7d32,stroke-width:2px,color:#000;
    class H,HG warn;
    class S,SF,SQ ok;

Instead of DELETE FROM customers WHERE id = 42, set a flag:

UPDATE customers SET deleted_at = NOW() WHERE id = 42;

• I keep audit history
• I can recover accidentally deleted data
• Foreign references don’t break

I add a default scope or a WHERE deleted_at IS NULL to my queries so deleted records are invisible by default. Most ORMs support this natively — JPA has @Where, Spring Data has @SoftDelete.

Always Add Audit Columns

I want created_at and updated_at on every table that mutates — I treat those as non-negotiable. I add created_by / updated_by (or a service identity) when I care about accountability — which is most business tables, but I skip them on tiny reference data if nothing meaningful would go there.

CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    -- ... business columns ...
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by  VARCHAR(100),
    updated_by  VARCHAR(100)
);

Every production debug session eventually becomes “when did this change, and who changed it?” Without at least timestamps — and actor columns where it matters — I’m flying blind.

Use Consistent Naming Conventions

I pick a convention and enforce it ruthlessly:

Element	Convention	Example
Tables	snake_case, plural	order_items
Columns	snake_case	customer_id
Primary keys	id	orders.id
Foreign keys	<singular_table>_id	orders.customer_id
Indexes	idx_<table>_<columns>	idx_orders_customer_id
Booleans	is_ prefix	is_active
Timestamps	_at suffix	created_at, deleted_at

The specific convention matters less than consistency. When every table follows the same pattern, I can navigate the schema without checking documentation — and so can anyone who joins the team after me.

---

The Takeaway

Database design is not glamorous work. Nobody tweets about a well-normalized schema. But every production nightmare I’ve debugged — data inconsistency, mysterious slowdowns, impossible migrations — traced back to a design decision made (or not made) in the first week.

Normalization keeps my facts in one place. Logical foreign keys keep my architecture flexible when the database can’t enforce every edge. Killing N+1 queries keeps my app fast at the ORM layer. Optimistic or pessimistic locking keeps concurrent updates from stepping on each other. And the small practices — indexes, soft deletes, audit columns, naming conventions — compound into a schema that’s a pleasure to work with instead of a minefield.

I design my databases as if the next developer to work on them is a sleep-deprived version of me — because it will be.