Data Modeling, Databases and Persistence

Introduction

A web application becomes useful when it can preserve information beyond the lifetime of a single HTTP request. User accounts, products, orders, payments, messages, and audit records must be stored, retrieved, updated, and protected reliably. This capability is called persistence.

Persistence is not merely a matter of writing data to a file. A professional system must decide what data means, how entities relate, which values are valid, how concurrent changes are handled, how queries remain efficient, and how confidential information is protected. These decisions are expressed through data models, database schemas, constraints, transactions, indexes, application code, and migration procedures.

This chapter focuses on relational databases and SQL while also comparing document, key-value, wide-column, and graph databases. It introduces object-relational mapping (ORM), secure CRUD operations, normalization, transaction processing, indexing, and schema evolution.

Learning Objectives

By the end of this chapter, you should be able to:

• Model application entities, attributes, relationships, and constraints.
• Design relational schemas using keys, foreign keys, and normalization.
• Write secure SQL for create, read, update, and delete operations.
• Explain transactions, isolation, concurrency problems, and recovery.
• Select and evaluate indexes for common query patterns.
• Use ORM concepts without losing awareness of SQL and database behavior.
• Compare relational and NoSQL approaches.
• Plan reliable migrations, backups, validation, and auditability.

1. From Application Requirements to a Data Model

A data model is a structured description of the information an application stores and the relationships among that information. Modeling begins with requirements rather than tables.

Consider an online bookstore. Requirements may state that a customer can place many orders, an order contains one or more books, and the price paid for a book must remain recorded even if the catalogue price later changes. These statements identify entities, relationships, cardinalities, and historical rules.

1.1 Entities, attributes, and relationships

An entity is a distinguishable object or concept, such as Customer, Book, or Order. An attribute describes an entity, such as a customer’s email address or a book’s title. A relationship describes how entities are connected.

Common cardinalities are:

A useful design distinguishes facts from derived values. An order total can be calculated from line quantities and prices, but storing it may improve reporting performance. If it is stored, the design must define how it is kept consistent.

1.2 Keys and integrity constraints

A candidate key is a minimal attribute set that uniquely identifies a row. A primary key is the candidate key chosen as the table’s principal identifier. A foreign key references a key in another table and represents referential integrity.

Important constraints include:

• NOT NULL: a value is required.
• UNIQUE: duplicate values are prohibited.
• CHECK: values must satisfy a predicate.
• PRIMARY KEY: identifies rows and is unique and non-null.
• FOREIGN KEY: prevents references to nonexistent parent rows, subject to configured actions.

Natural keys, such as an email address, have business meaning. Surrogate keys, such as generated integers or UUIDs, have no direct business meaning. Surrogate keys can simplify relationships, but they do not remove the need for unique constraints on business identifiers.

1.3 Relational schema example

A normalized bookstore schema can contain:

CREATE TABLE customer (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(320) NOT NULL UNIQUE,
    full_name VARCHAR(120) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE book (
    book_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn VARCHAR(17) NOT NULL UNIQUE,
    title VARCHAR(300) NOT NULL,
    current_price DECIMAL(10,2) NOT NULL CHECK (current_price >= 0)
);

CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL CHECK (status IN ('PENDING','PAID','CANCELLED')),
    ordered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

CREATE TABLE order_item (
    order_id BIGINT NOT NULL,
    book_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, book_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (book_id) REFERENCES book(book_id)
);

The composite primary key in order_item prevents the same book from appearing twice in one order. unit_price is copied deliberately because it records the price at purchase time, not the book’s changing catalogue price.

2. Normalization and Relational Design

Normalization reduces undesirable redundancy and update anomalies. It is based on functional dependencies: if attribute set X determines attribute Y, this is written conceptually as X → Y.

2.1 Normal forms

• First Normal Form (1NF): each field contains an atomic value under the chosen data model; repeating groups are separated.
• Second Normal Form (2NF): the relation is in 1NF and every non-key attribute depends on the whole candidate key, not merely part of a composite key.
• Third Normal Form (3NF): the relation is in 2NF and non-key attributes do not depend transitively on another non-key attribute.
• Boyce–Codd Normal Form (BCNF): every determinant of a nontrivial functional dependency is a candidate key.

Suppose a table contains (order_id, book_id, quantity, customer_id, customer_name). If the key is (order_id, book_id), then quantity depends on the whole key, but customer_id depends only on order_id, and customer_name depends on customer_id. Splitting the data into Order, Customer, and OrderItem removes partial and transitive dependencies.

Normalization improves consistency, but it may require joins. Controlled denormalization can be justified for read-heavy workloads, reporting, caching, or search indexes. It should be documented and supported by a consistency strategy rather than applied casually.

3. SQL and Secure CRUD Operations

SQL expresses data definition, manipulation, and querying. CRUD means Create, Read, Update, and Delete.

3.1 Parameterized queries

Applications must not construct SQL by concatenating untrusted input. Instead, use bound parameters:

SELECT customer_id, full_name, email
FROM customer
WHERE email = :email;

The database driver sends the SQL structure separately from the value. This prevents ordinary input from being interpreted as SQL syntax. Input validation remains necessary for correctness, but validation is not a substitute for parameterization.

For dynamic sorting or column selection, parameter binding usually cannot represent identifiers. The application should select from a fixed allow-list, for example mapping "name" to the known SQL fragment full_name, rather than accepting arbitrary text.

3.2 CRUD patterns

-- Create
INSERT INTO book (isbn, title, current_price)
VALUES (:isbn, :title, :price);

-- Read
SELECT book_id, isbn, title, current_price
FROM book
WHERE book_id = :book_id;

-- Update with ownership or authorization condition
UPDATE orders
SET status = :new_status
WHERE order_id = :order_id
  AND customer_id = :authenticated_customer_id;

-- Delete only when business rules permit it
DELETE FROM book
WHERE book_id = :book_id;

A web application must also enforce authorization. A valid SQL query can still be insecure if it allows one user to retrieve another user’s records. Authorization predicates, service-layer policies, database roles, and row-level security can work together.

Avoid exposing raw database errors to users. Log diagnostic details securely, return a suitable application error, and avoid logging passwords, tokens, or unnecessary personal data.

4. Transactions, Concurrency, and Reliability

A transaction groups operations into one logical unit. The classical ACID properties are:

• Atomicity: all operations succeed or none take effect.
• Consistency: declared constraints and business invariants remain valid after commit.
• Isolation: concurrent transactions behave according to the selected isolation guarantees.
• Durability: committed data survives ordinary failures through logging and recovery mechanisms.

A transaction does not automatically guarantee every business rule; application logic and database constraints are both important.

4.1 Worked transaction example

When placing an order, the application may insert an order, insert its items, decrement inventory, and record payment intent. If inventory is insufficient, all related changes should be rolled back.

BEGIN;

INSERT INTO orders (customer_id, status)
VALUES (:customer_id, 'PENDING')
RETURNING order_id;

INSERT INTO order_item (order_id, book_id, quantity, unit_price)
VALUES (:order_id, :book_id, :quantity, :unit_price);

UPDATE inventory
SET available = available - :quantity
WHERE book_id = :book_id
  AND available >= :quantity;

-- Application verifies that exactly one inventory row was updated.
COMMIT;

If the update affects zero rows, the application rolls back. Checking the affected-row count is essential; merely executing the statement is not proof that stock was reserved.

4.2 Isolation and anomalies

Concurrent transactions can produce:

• Dirty read: one transaction reads uncommitted changes from another.
• Non-repeatable read: the same row returns different committed values during one transaction.
• Phantom read: a repeated predicate query returns a changed set of rows.
• Lost update: one write overwrites another without detecting the conflict.

Database systems offer isolation levels such as Read Committed, Repeatable Read, and Serializable. Higher isolation can reduce anomalies but may increase locking, waiting, or transaction aborts. Optimistic concurrency is another approach: a row includes a version number, and an update succeeds only when the expected version still matches.

UPDATE profile
SET full_name = :name, version = version + 1
WHERE profile_id = :id AND version = :old_version;

If zero rows are affected, the application reports a conflict rather than silently overwriting another user’s changes.

Transactions should be short, should not wait for external network calls, and should be retried only when the operation is safe to repeat. Retry logic requires idempotency keys or carefully designed unique constraints to prevent duplicate effects.

5. Indexes and Query Performance

An index is an auxiliary data structure that helps locate rows without scanning every row. It improves some reads but consumes storage and adds work to inserts, updates, and deletes.

Common indexes include:

• B-tree indexes for equality, ordering, and range predicates.
• Hash indexes for equality in systems that support them reliably.
• Full-text indexes for linguistic search.
• Spatial indexes for geographic data.
• Composite indexes over multiple columns.
• Partial or filtered indexes over a selected subset of rows.

Index design follows actual access patterns. For example:

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, ordered_at DESC);

This can support queries filtering by customer_id and ordering by date. In a composite index, column order matters. An index on (customer_id, ordered_at) is generally more useful for a predicate beginning with customer_id than for a query filtering only on ordered_at.

Use query plans, such as EXPLAIN, to inspect whether an index is used and whether the estimated cost is reasonable. Avoid indexing every column: low-selectivity indexes, redundant indexes, and unused indexes can reduce write performance. Indexes should be reviewed as data volume and query patterns change.

6. Object-Relational Mapping

An ORM maps programming-language objects to relational tables. A Customer class may map to customer, while an Order object may contain associated OrderItem objects.

ORM benefits include:

• Reduced repetitive SQL for routine operations.
• Domain-oriented programming interfaces.
• Automatic mapping of types and relationships.
• Migration and validation integration in many frameworks.

ORM risks include:

• The N+1 query problem, where one query loads parents and one additional query runs for each child.
• Accidental eager loading of large relationships.
• Inefficient generated SQL.
• Confusing object identity with database identity.
• Hidden transaction boundaries and unexpected cascade deletes.

A professional developer understands both the ORM and the underlying SQL. Use eager loading, batch loading, projections, or explicit joins when appropriate, and inspect generated queries in development and testing.

7. Relational and NoSQL Alternatives

NoSQL is not one database model. Major categories include:

The choice should follow access patterns, consistency requirements, scale, operational expertise, and compliance needs. A document database may store an order and its items together, reducing joins, but repeated embedded data can complicate updates. A relational database may be preferable when many entities must be joined consistently or when constraints are central.

Polyglot persistence uses more than one storage technology. It can be effective, but it introduces synchronization, monitoring, backup, and consistency complexity. It should solve a demonstrated requirement, not reflect technology fashion.

8. Migrations and Schema Evolution

A migration is a versioned, repeatable change to database structure or controlled data. Examples include adding a column, creating an index, splitting a table, or changing a constraint.

A safe migration process includes:

1. Store migration scripts in version control.
2. Apply them automatically or through a controlled deployment process.
3. Make compatible changes before application code depends on them.
4. Backfill large datasets in batches.
5. Monitor locks, duration, errors, and replication lag.
6. Remove obsolete structures only after old application versions are gone.

A common expand-and-contract sequence is:

• Expand: add a nullable column or new table.
• Deploy code that writes both old and new representations.
• Backfill existing records and validate them.
• Switch reads to the new representation.
• Contract: remove the old column after a safe period.

A migration is not the same as a backup. Backups must be tested through restoration exercises, and recovery objectives should be defined. Point-in-time recovery, replication, and geographically separate copies may be required for important systems.

9. Case Study: Reliable Inventory Reservation

Background

The following is an illustrative case. A small online retailer experiences overselling during promotional events. Two customers can purchase the last item at nearly the same time.

Problem

The original application reads available stock, checks that it is positive, and later updates the value. Between the read and update, another request can reserve the same item. This is a race condition.

Analysis

The business invariant is that available inventory must never become negative. Application-level checks alone are insufficient because concurrent requests can observe the same old value.

Approach

The team uses a transaction and an atomic conditional update:

BEGIN;

UPDATE inventory
SET available = available - :quantity
WHERE book_id = :book_id
  AND available >= :quantity;

-- Require affected_rows = 1.
-- If not, ROLLBACK and report insufficient stock.

INSERT INTO reservation (order_id, book_id, quantity)
VALUES (:order_id, :book_id, :quantity);

COMMIT;

The inventory row is protected by the database’s concurrency mechanism. A unique constraint on an idempotency key prevents a client retry from creating duplicate reservations. The application records audit events and returns a clear result to the customer.

Outcome

Concurrent requests are serialized for the relevant inventory row, and the invariant is enforced close to the data. Monitoring measures failed reservations, transaction latency, lock waits, and duplicate-request attempts.

Learning points

Database constraints and atomic updates are stronger than informal application assumptions. Correctness requires identifying the invariant, designing a transaction around it, and testing concurrent behavior rather than testing only sequential examples.

10. Governance, Privacy, and Contemporary Practice

Data design has ethical and legal consequences. Collect only data required for a legitimate purpose, define retention periods, restrict access, encrypt data in transit and at rest where appropriate, and support correction or deletion requirements applicable to the jurisdiction. Passwords must be stored using a slow, salted password-hashing algorithm, never reversible encryption or plaintext.

Use least-privilege database accounts: a reporting account should not be able to drop tables, and an application account should receive only required permissions. Maintain audit trails for sensitive operations while minimizing unnecessary personal information in logs.

Contemporary systems increasingly combine transactional databases with caches, search engines, analytics warehouses, and AI services. AI-generated SQL or schema suggestions can accelerate development, but generated code must be reviewed for injection risks, incorrect joins, privacy leakage, missing constraints, and unsuitable performance. AI does not replace query plans, tests, threat modeling, or human accountability.

Best-Practice Checklist

• Begin with business entities, invariants, and access patterns.
• Use primary keys, foreign keys, and explicit constraints.
• Normalize first; denormalize only for a measured reason.
• Parameterize every value supplied by a user or external system.
• Enforce authorization, not merely authentication.
• Use transactions for related changes and verify affected rows.
• Choose indexes from measured query patterns and inspect query plans.
• Understand ORM-generated SQL and avoid N+1 queries.
• Version migrations and use expand-and-contract deployment patterns.
• Test backups, recovery, concurrency, and migration rollback procedures.
• Minimize personal data and apply least privilege.

Chapter Summary

Data modeling translates application requirements into entities, attributes, relationships, keys, and constraints. Relational databases provide structured schemas, joins, SQL, and strong integrity mechanisms. Normalization reduces redundancy and anomalies, while justified denormalization can support performance.

Secure CRUD operations require parameterized SQL, authorization checks, validation, and careful error handling. Transactions provide atomicity and durability, but developers must understand isolation, concurrency anomalies, affected-row checks, and idempotency. Indexes accelerate appropriate query patterns at a cost to storage and write performance. ORMs improve productivity but do not eliminate the need to understand SQL.

NoSQL systems offer alternative models optimized for particular access patterns and scalability requirements. Database choice should be evidence-based. Finally, migrations, backups, privacy controls, auditing, and least privilege are essential parts of professional persistence engineering.

Key Terms

Data model: A description of stored data, relationships, and constraints.

Persistence: Retention of application data beyond one program execution or request.

Entity: A distinguishable object or concept represented in a data system.

Primary key: The selected unique, non-null identifier for a table row.

Foreign key: A column or column set referencing a key in another table.

Candidate key: A minimal attribute set that uniquely identifies a row.

Constraint: A rule enforced on allowable data.

Normalization: Structuring relations to reduce redundancy and anomalies.

Functional dependency: A relationship in which one attribute set determines another.

Transaction: A logical unit of database work.

ACID: Atomicity, Consistency, Isolation, and Durability properties.

Isolation level: The concurrency guarantee provided to a transaction.

Index: An auxiliary structure that accelerates selected queries.

CRUD: Create, Read, Update, and Delete operations.

ORM: Software mapping objects in a programming language to relational data.

Migration: A versioned change to database schema or controlled data.

Idempotency: The property that repeating an operation produces the same intended effect.

Denormalization: Deliberate duplication or restructuring to improve performance or access simplicity.

Referential integrity: Consistency between foreign-key references and referenced rows.

NoSQL: A broad class of non-relational database models.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Why is unit_price stored in an order-item table even when a book has a current price?
3. Define atomicity and isolation in the context of transactions.
4. What problem does parameterized SQL prevent?
5. State one benefit and one risk of using an ORM.

Long Answer Questions

1. Explain normalization through Third Normal Form, using an order-processing example.
2. Describe how indexes work, including the importance of column order in composite indexes.
3. Compare relational, document, key-value, and graph databases for different application requirements.
4. Explain the expand-and-contract approach to database migrations.
5. Discuss how authorization, constraints, transactions, and auditing combine to secure CRUD operations.

Analytical / Application Questions

1. Design a schema for a course-registration system in which students enroll in many courses and each enrollment receives a grade. Identify keys, constraints, and the many-to-many relationship.
2. An application reads inventory, waits for payment confirmation, and then decrements stock. Analyze the concurrency and reliability risks and propose a safer transaction design.
3. A query filters orders by customer_id, restricts status = 'PAID', and sorts by ordered_at descending. Propose an index strategy, explain trade-offs, and state how you would validate it experimentally.

Multiple Choice Questions with Answers

Question 1

Which structure correctly represents a many-to-many relationship in a relational database?

A. A nullable column in either table

B. A junction table containing foreign keys to both entities

C. A duplicated copy of one entire table

D. A single text field containing comma-separated identifiers

Correct Answer: B. A junction table containing foreign keys to both entities

Question 2

What is the primary purpose of a foreign key?

A. To encrypt a column

B. To sort every query automatically

C. To enforce a valid reference to a related row

D. To replace all indexes

Correct Answer: C. To enforce a valid reference to a related row

Question 3

Which SQL practice most directly prevents ordinary SQL injection through user values?

A. Using longer table names

B. Using parameterized queries

C. Adding more application comments

D. Disabling indexes

Correct Answer: B. Using parameterized queries

Question 4

Which ACID property means that a transaction’s changes are all applied or none are applied?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: A. Atomicity

Question 5

What is a lost update?

A. A deleted index being recreated

B. One concurrent write silently overwriting another

C. A query returning no rows because of a foreign key

D. A transaction surviving a restart

Correct Answer: B. One concurrent write silently overwriting another

Question 6

Why is an index on (customer_id, ordered_at) generally useful for filtering by customer_id and sorting by ordered_at?

A. The leading indexed column matches the principal filter

B. Indexes eliminate the need for constraints

C. The index stores passwords securely

D. Composite indexes always improve every query

Correct Answer: A. The leading indexed column matches the principal filter

Question 7

Which design best records the price paid at the time of purchase?

A. Read the catalogue price whenever the order is displayed

B. Store unit_price in the order-item record

C. Delete the catalogue price after every sale

D. Store the price only in a browser cookie

Correct Answer: B. Store unit_price in the order-item record

Question 8

What is the main purpose of an expand-and-contract migration?

A. To make incompatible schema changes in one untested step

B. To support compatibility while application versions transition

C. To replace backups

D. To remove all constraints temporarily

Correct Answer: B. To support compatibility while application versions transition

Question 9

Which is a common ORM performance problem?

A. N+1 queries caused by repeatedly loading related records

B. Primary keys becoming encrypted automatically

C. Foreign keys being converted into images

D. Transactions becoming impossible in every database

Correct Answer: A. N+1 queries caused by repeatedly loading related records

Question 10

Which statement about denormalization is most accurate?

A. It is always required for relational databases

B. It removes the need for authorization

C. It may improve measured read performance but increases consistency responsibility

D. It guarantees serializable transactions

Correct Answer: C. It may improve measured read performance but increases consistency responsibility