Data Modeling, Databases, and Persistence

Introduction

Most web applications depend on data that must outlive an individual request. User accounts, products, orders, payments, messages, permissions, and audit records must be stored, retrieved, updated, protected, and eventually archived or deleted. Persistence is therefore not merely a programming concern; it is a design problem involving semantics, integrity, performance, scalability, security, and governance.

A web application's data layer usually contains three related but distinct elements:

1. The domain model: concepts meaningful to the business, such as Customer, Order, or Course.
2. The logical data model: entities, attributes, relationships, and constraints.
3. The physical implementation: tables, indexes, documents, files, database servers, replicas, and backup systems.

A sound design preserves important business rules while allowing efficient access patterns. This chapter examines relational and document-oriented models, SQL, normalization, indexing, transactions, object-relational mapping, migrations, concurrency, backups, and technology selection.

Learning Objectives

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

• model application data using entities, attributes, relationships, and constraints;
• distinguish relational and document-oriented databases;
• write SQL for definition, querying, insertion, updating, and deletion;
• apply normalization without ignoring practical performance requirements;
• select indexes based on access patterns;
• explain transactions, isolation, locking, and optimistic concurrency;
• use ORM tools responsibly and recognize their limitations;
• plan schema migrations, backups, recovery, and retention;
• evaluate consistency, scalability, security, and governance when choosing a database technology.

1. Data Modeling as an Engineering Activity

A data model is an explicit description of the data a system stores and the relationships among data items. Modeling begins with requirements rather than with a preferred database product. A useful sequence is:

1. Identify business concepts and events.
2. Identify properties and identifiers.
3. Identify relationships and their cardinalities.
4. State rules that must always hold.
5. Identify important read and write patterns.
6. Map the model to a database technology.

An entity is a distinguishable object or event. An attribute describes an entity. An identifier uniquely distinguishes an entity. A relationship connects entities. For example, a customer may place many orders, while each order belongs to one customer. This is a one-to-many relationship.

Cardinality describes how many instances participate in a relationship: one-to-one, one-to-many, or many-to-many. A many-to-many relationship normally requires an associative entity. For example, Students and Courses are connected through Enrollment, which may also contain grade and enrollment date.

Keys and constraints

A primary key identifies each row. A foreign key references a key in another table and expresses referential integrity. A candidate key is any minimal attribute set that can uniquely identify a row; one candidate key is selected as the primary key. A surrogate key, such as an integer or UUID, has no direct business meaning. A natural key, such as a national registration number, derives from the domain. Surrogate keys simplify many relationships, but natural keys may still require a unique constraint.

Important constraints include:

• NOT NULL: a value is required;
• UNIQUE: duplicate values are forbidden;
• CHECK: a predicate must be true;
• PRIMARY KEY: values are unique and non-null;
• FOREIGN KEY: referenced records must exist, subject to defined delete/update behavior.

Constraints should be enforced in the database when they represent invariants. Application validation improves user feedback, but database constraints protect against bugs, scripts, concurrent requests, and other applications that bypass the original code.

2. Relational Data Models

The relational model represents data as relations, commonly implemented as tables. A table contains rows, and each row contains values in named columns. Relational algebra provides operations such as selection, projection, union, and join; SQL expresses these operations in a practical language.

A relational design is appropriate when:

• relationships and integrity rules are important;
• data is frequently queried in combinations;
• transactions must cover multiple entities;
• reporting and ad hoc queries are significant;
• the schema is reasonably stable and structured.

Example schema

Consider an online bookstore:

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

CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0)
);

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

CREATE TABLE order_items (
    order_id BIGINT NOT NULL REFERENCES orders(order_id),
    product_id BIGINT NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);

The unit_price is stored in order_items even though products has a price. This is intentional: an order is a historical record, so later price changes must not alter the amount originally charged.

SQL operations

A parameterized query retrieves a customer's orders:

SELECT o.order_id, o.status, o.ordered_at,
       SUM(oi.quantity * oi.unit_price) AS total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.customer_id = :customer_id
GROUP BY o.order_id, o.status, o.ordered_at
ORDER BY o.ordered_at DESC;

Parameters such as :customer_id should be bound by the database driver. Constructing SQL by string concatenation creates SQL-injection risk.

SQL uses NULL to represent an absent or unknown value. It is not equivalent to zero or an empty string. Comparisons involving NULL use three-valued logic; therefore, use IS NULL or IS NOT NULL, not = NULL.

3. Normalization and Deliberate Denormalization

Normalization reduces redundancy and update anomalies. The principal normal forms used in application design are:

• First Normal Form (1NF): each attribute contains atomic values and repeating groups are represented separately.
• Second Normal Form (2NF): the relation is in 1NF and every non-key attribute depends on the whole candidate key, relevant especially to composite keys.
• Third Normal Form (3NF): the relation is in 2NF and non-key attributes do not depend transitively on a key through another non-key attribute.

Suppose a table contains (order_id, product_id, product_name, quantity). If the key is (order_id, product_id), product_name depends only on product_id, not on the whole key. Moving product information to Products removes this partial dependency.

Normalization helps prevent:

• Update anomalies: the same fact must be changed in multiple rows;
• Insertion anomalies: a fact cannot be inserted without an unrelated fact;
• Deletion anomalies: deleting one fact accidentally removes another.

Denormalization intentionally duplicates or precomputes data to improve read performance or simplify access. Examples include storing a customer display name in an event record or maintaining a summary table. Denormalization introduces synchronization responsibilities and should be justified by measured access patterns, not used as a substitute for understanding the model.

4. Document-Oriented Models

A document database stores records as documents, commonly represented in JSON-like form. A bookstore order might be represented as:

{
  "orderId": "o-1042",
  "customerId": "c-19",
  "status": "PAID",
  "orderedAt": "2025-03-08T10:30:00Z",
  "items": [
    {"productId": "p-7", "title": "Web Engineering", "quantity": 1, "unitPrice": 42.00}
  ]
}

Document modeling often begins with questions such as: What data is read together? What data changes together? What is bounded in size? A bounded one-to-few relationship may be embedded. A large or independently managed collection is commonly referenced by identifier.

Embedding can provide a single-read aggregate and atomic updates within a document. Referencing avoids unbounded document growth and duplication, but may require multiple queries or application-level joins. Document stores are useful for variable structures, content catalogs, event records, and aggregates with clear ownership. They can be a poor choice when many-to-many relationships, complex joins, and strict cross-record constraints dominate.

Document-oriented does not automatically mean schema-free. Production systems should validate document shape, version documents, define required fields, and plan for old versions. Flexibility in storage can otherwise become inconsistency in the application.

Relational and document comparison

The choice should follow domain requirements and access patterns, not fashion.

5. Indexes and Query Performance

An index is an auxiliary data structure that helps locate rows without scanning the entire table. Common indexes are B-tree indexes; hash, full-text, spatial, and specialized indexes serve other workloads. Indexes accelerate selected reads but consume storage and make inserts, updates, and deletes more expensive.

A useful index generally supports columns in filtering, joining, sorting, or uniqueness operations. For a query filtering by customer_id and ordering by ordered_at, the following may help:

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

Column order matters. A composite index beginning with customer_id is well suited to queries that constrain customer, but may be less useful for queries involving only ordered_at. Index design must be verified with an execution plan, such as EXPLAIN, using realistic data volumes.

Avoid indexing every column. Low-selectivity columns, such as a Boolean status with only two values, may not benefit from a simple index unless combined with other columns or used in a selective workload. Indexes should be reviewed as requirements and data distributions change.

6. Transactions and Consistency

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

• Atomicity: all operations succeed or none take effect;
• Consistency: committed results satisfy declared and application invariants;
• Isolation: concurrent transactions do not produce prohibited interference;
• Durability: committed results survive specified failures.

For example, placing an order may insert an order, insert its items, and reduce stock. If stock reduction fails, the transaction should not leave a partially created order.

BEGIN;

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

UPDATE products
SET stock_quantity = stock_quantity - :quantity
WHERE product_id = :product_id
  AND stock_quantity >= :quantity;

-- Application checks that exactly one row was updated.
COMMIT;

If the stock update affects zero rows, the application should roll back. The database transaction alone cannot infer every business condition.

Isolation levels trade concurrency for protection against anomalies. Common anomalies include dirty reads, non-repeatable reads, phantom reads, and lost updates. Implementations differ, especially for the meaning of REPEATABLE READ and snapshot-based isolation. Developers must consult the selected database's documentation.

A transaction should be short and bounded. Holding locks while making network calls or waiting for user input increases contention and deadlock risk. Deadlocks are normal possibilities in concurrent systems; applications should use consistent lock ordering and safely retry transactions where appropriate.

7. Concurrency Control

Two users may read the same stock value and both attempt to update it. Without protection, one update may overwrite the other. Pessimistic concurrency acquires a lock before changing data, for example with SELECT ... FOR UPDATE. Optimistic concurrency detects that data changed after it was read.

A common optimistic technique adds a version column:

UPDATE products
SET stock_quantity = :new_quantity,
    version = version + 1
WHERE product_id = :id
  AND version = :old_version;

If the affected-row count is zero, another transaction changed the product, and the application must reread, merge, or report a conflict. This technique is useful when conflicts are relatively uncommon and long-held locks are undesirable.

Idempotency is also important in web systems. A payment or order request may be retried because of a timeout even though the server completed it. An idempotency key, stored with the operation result under a uniqueness constraint, prevents accidental duplicate processing.

8. Object-Relational Mapping

An ORM maps program objects to relational tables. It can provide identity mapping, relationship loading, change tracking, transactions, and parameterized queries. ORMs improve productivity, but they do not eliminate database knowledge.

Important ORM risks include:

• N+1 queries: one query loads parent records and an additional query loads children for each parent;
• accidental eager loading of large relationships;
• inefficient generated SQL;
• hidden transactions and unexpected writes;
• mismatches between object inheritance and relational structure;
• misuse of generic repositories that hide important query semantics.

Developers should inspect generated SQL, use explicit projections, paginate large results, and apply eager loading or batch loading deliberately. Complex reporting queries may be clearer and faster as carefully reviewed SQL rather than ORM expressions.

9. Migrations and Schema Evolution

A migration is a versioned, repeatable change to database structure or data. A migration history allows development, testing, deployment, and rollback procedures to be coordinated.

A safe production migration commonly follows an expand-and-contract pattern:

1. Add a nullable column or new structure.
2. Deploy code that can read both old and new forms.
3. Backfill data in small batches.
4. Begin writing the new form.
5. Verify completeness and performance.
6. Enforce constraints after invalid data is removed.
7. Remove the old structure in a later release.

Large table rewrites can lock tables or consume substantial resources. Migrations should be tested against production-like data, monitored, and accompanied by a recovery plan. A migration rollback is not always as simple as reversing SQL; irreversible data transformations require backups or forward-fix strategies.

10. Backups, Recovery, and Data Governance

A backup is useful only if it can be restored. Backup planning should specify:

• Recovery Point Objective (RPO): the maximum acceptable amount of data loss measured in time;
• Recovery Time Objective (RTO): the maximum acceptable time to restore service.

For example, an RPO of 15 minutes means that losing up to 15 minutes of committed changes is acceptable under the stated plan. Point-in-time recovery, replication, snapshots, and logical exports address different failure modes. Replication improves availability but is not a substitute for backups: corruption or accidental deletion may replicate immediately.

Use encrypted backups, restricted access, retention schedules, and regular restore tests. Protect credentials and encryption keys separately from backup data. Data should be classified according to sensitivity, and collection should be limited to legitimate purposes. Personal data may be subject to privacy laws, deletion rights, retention limits, breach notification obligations, and cross-border transfer rules. Audit trails should themselves avoid unnecessary sensitive information.

11. Applied Case: Course Registration Service

Background

Consider an illustrative university course-registration service. Students search courses, enroll, drop courses, and view schedules. A course has a capacity, and a student must not enroll twice in the same course offering.

Problem

During registration deadlines, many requests arrive simultaneously. An implementation that first reads available seats and then updates enrollment occasionally exceeds capacity. Students also report duplicate enrollments after browser retries.

Analysis

The core invariants are:

• a student may have at most one enrollment per offering;
• confirmed enrollments must not exceed capacity;
• an enrollment and the seat allocation must be changed atomically;
• repeated requests with the same idempotency key must not create duplicates.

Approach

A relational model uses students, offerings, and enrollments. A unique constraint on (student_id, offering_id) prevents duplicates. Registration executes in a transaction, locks the offering row, checks capacity, inserts the enrollment, and commits. The service records an idempotency key with the outcome.

The lock is held only for the transaction. The application returns a clear conflict response when capacity is exhausted or a duplicate enrollment is detected. An index on (offering_id, status) supports administrative capacity queries, while the unique constraint supplies an index suitable for duplicate detection.

Outcome

The design moves correctness-critical rules into database constraints and transaction boundaries. It also makes failure behavior explicit: clients may safely retry a request, but concurrent registrations are serialized for the affected offering.

Learning points

This case illustrates that performance, correctness, and user experience are connected. A fast operation that permits over-enrollment is not successful. Conversely, locking the entire database would be unnecessary; contention can be limited to the offering being modified.

12. Technology Selection and Contemporary Practice

Technology selection should evaluate workload shape, consistency requirements, operational capability, cost, ecosystem maturity, portability, and regulatory obligations. Questions include:

• Are relationships and multi-entity transactions central?
• Are reads mostly aggregate-shaped?
• What are the latency and throughput targets?
• How large and variable are records?
• Is horizontal partitioning required?
• Can the team operate the technology and restore it under pressure?

Polyglot persistence may use different stores for different workloads, such as a relational system for transactions and a search engine for full-text retrieval. It increases operational complexity and creates synchronization problems; it should be adopted for a demonstrated need.

Cloud-managed databases reduce infrastructure work but do not remove responsibility for schema design, permissions, backups, cost control, or recovery testing. Change-data-capture pipelines, event-driven architectures, serverless databases, and distributed SQL systems are important contemporary developments, but each introduces consistency, observability, latency, or vendor-dependence trade-offs.

Artificial intelligence can assist with schema suggestions, query explanation, test-data generation, and anomaly detection. Generated SQL must be reviewed for correctness, security, privacy, and performance. Sensitive production data should not be supplied to tools without authorization and appropriate safeguards.

Best Practices

1. Model business invariants explicitly.
2. Prefer clear constraints over informal assumptions.
3. Use parameterized SQL.
4. Design indexes from measured access patterns.
5. Keep transactions short and retry safe conflicts where appropriate.
6. Inspect ORM-generated queries.
7. Version migrations and test them on realistic data.
8. Monitor slow queries, lock waits, errors, and storage growth.
9. Test restoration, not merely backup creation.
10. Minimize sensitive data and control its lifecycle.
11. Document consistency assumptions and failure behavior.
12. Choose technology according to requirements rather than popularity.

Challenges and Limitations

No single model optimizes every objective. Normalization can require joins; denormalization creates synchronization work. Strong consistency may reduce availability or increase latency across distant regions. Indexes improve reads but increase write cost. Replication can introduce stale reads. ORMs improve development speed but may obscure execution. Document flexibility can accelerate early development but make long-term governance difficult.

These are engineering trade-offs, not defects that can be eliminated by selecting a fashionable product. Good design makes assumptions visible, measures actual behavior, and plans for failure.

Chapter Summary

Data persistence is a foundational part of web application architecture. Relational models represent structured entities and relationships with tables, keys, joins, and declarative constraints. Document models organize data around aggregates and access patterns, often embedding related data. Normalization reduces anomalies, while deliberate denormalization may improve performance at the cost of consistency work.

Indexes should be designed from real queries and verified with execution plans. Transactions provide atomicity and isolation, while pessimistic and optimistic concurrency techniques protect shared state. ORMs are useful abstractions but require SQL and database literacy. Migrations must support gradual, observable schema evolution. Backups, restoration tests, RPO, RTO, privacy, access control, and retention form part of persistence engineering. Ultimately, database technology should be selected by domain relationships, consistency needs, workload, scalability, operational capability, and governance requirements.

Key Terms

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

Attribute: A property describing an entity.

Backup: A recoverable copy of data.

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

Constraint: A rule enforced on stored data.

Denormalization: Deliberate duplication or precomputation for performance or convenience.

Document database: A database that stores aggregate records as documents.

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

Idempotency: The property that repeating an operation has the same intended effect as performing it once.

Index: An auxiliary structure that accelerates data access.

Isolation level: A database-defined degree of protection among concurrent transactions.

Migration: A versioned change to database structure or data.

Normalization: Structuring relations to reduce redundancy and anomalies.

Object-relational mapping: Mapping between program objects and relational data.

Optimistic concurrency: Conflict detection performed when an update is committed.

Primary key: The selected key that uniquely identifies rows in a table.

Referential integrity: The requirement that references point to valid records.

RPO: Maximum acceptable time-based amount of data loss.

RTO: Maximum acceptable time to restore service.

Transaction: A logical unit of database work.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Why is unit_price commonly stored in an order-item record?
3. Define normalization and state one anomaly it prevents.
4. What is the purpose of an index?
5. Distinguish RPO from RTO.

Long Answer Questions

1. Compare relational and document-oriented data models, including their strengths and risks.
2. Explain ACID properties and illustrate them using an order-placement transaction.
3. Describe 1NF, 2NF, and 3NF with a suitable example.
4. Explain optimistic and pessimistic concurrency control and identify situations where each is appropriate.
5. Describe an expand-and-contract migration strategy and explain why it is useful in production.

Analytical / Application Questions

1. Design a database model for a library system containing members, books, physical copies, loans, and late fees. State keys, relationships, and at least five constraints.
2. A registration system occasionally exceeds course capacity during simultaneous requests. Propose a transaction and concurrency-control strategy, including the constraints you would enforce.
3. A product catalog contains highly variable specifications, while payments and orders require strict multi-record consistency. Recommend a persistence architecture and justify whether polyglot persistence is warranted.

Multiple Choice Questions with Answers

Question 1

Which constraint prevents two rows from having the same value for an email address?

A. CHECK

B. UNIQUE

C. FOREIGN KEY

D. DEFAULT

Correct Answer: B. UNIQUE

Question 2

What is the primary purpose of normalization?

A. To eliminate all indexes

B. To increase document size

C. To reduce redundancy and update anomalies

D. To guarantee zero query latency

Correct Answer: C. To reduce redundancy and update anomalies

Question 3

Which property ensures that a transaction's committed changes survive a specified system failure?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 4

Why should SQL parameters be bound rather than concatenated into query strings?

A. To prevent SQL injection and handle values safely

B. To remove the need for indexes

C. To disable transactions

D. To convert tables into documents

Correct Answer: A. To prevent SQL injection and handle values safely

Question 5

An update includes WHERE version = :old_version and affects zero rows. What most likely occurred?

A. The table has no primary key

B. Another transaction changed the record

C. The database automatically normalized the table

D. The query created a backup

Correct Answer: B. Another transaction changed the record

Question 6

Which relationship commonly requires an associative entity in a relational model?

A. One-to-one

B. One-to-many

C. Many-to-many

D. Self-reference only

Correct Answer: C. Many-to-many

Question 7

What is a major risk of replicating a database without maintaining independent backups?

A. Replication may copy accidental deletion or corruption

B. Replication always prevents scaling

C. Replication removes all constraints

D. Replication makes SQL impossible

Correct Answer: A. Replication may copy accidental deletion or corruption

Question 8

Which migration approach allows old and new application versions to coexist during deployment?

A. Expand-and-contract

B. Delete-and-recreate

C. Lock-and-wait

D. Query-and-ignore

Correct Answer: A. Expand-and-contract

Question 9

What does an idempotency key help prevent in a retried payment request?

A. Duplicate processing

B. Table normalization

C. Index creation

D. Database encryption

Correct Answer: A. Duplicate processing

Question 10

Why can an ORM produce an N+1 query problem?

A. It may issue one query for parents and one additional query per parent

B. It always deletes foreign keys

C. It prevents parameter binding

D. It converts every column to a Boolean

Correct Answer: A. It may issue one query for parents and one additional query per parent