Data Management and Persistence

Introduction

Modern web applications are not merely collections of pages and APIs; they are information systems whose value depends on the reliable creation, retrieval, modification, protection, and interpretation of data. A shopping application must preserve customers, products, orders, payments, and inventory. A learning platform must manage users, courses, assessments, submissions, and progress. A social application must handle highly connected and rapidly changing content.

Data persistence is the ability of an application to retain data beyond the lifetime of a single process or request. Data management includes the broader practices used to structure, query, secure, synchronize, optimize, and govern that data. Advanced web development therefore requires more than knowledge of SQL syntax. Developers must select suitable data models, design schemas, control transactions, optimize access, map application objects to storage, manage caches, and reason about consistency and failure.

This chapter examines relational and NoSQL databases as complementary technologies. It also explains indexing, transactions, object–relational mapping (ORM), caching, consistency strategies, and professional data-governance concerns.

Learning Objectives

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

1. Explain persistence, data models, schemas, keys, constraints, and normalization.
2. Design relational schemas and write representative SQL queries.
3. Compare document, key–value, column-family, and graph databases with relational systems.
4. Apply transactions, isolation levels, and concurrency-control principles.
5. Explain ORM mapping, its benefits, and its performance risks.
6. Design indexes using workload analysis rather than intuition alone.
7. Select and implement suitable caching and consistency strategies.
8. Evaluate a data architecture in terms of reliability, scalability, security, and governance.

1. Data Models and Persistence Architecture

A database is an organized system for storing and retrieving data. A database management system (DBMS) provides storage, query processing, concurrency control, recovery, security, and administration. The application normally communicates with the DBMS through a driver or data-access library.

A typical request path is:

Browser or mobile client → web server → application service → data-access layer → database or cache

The application service implements business rules. The data-access layer translates application operations into SQL, document queries, or other database commands. A cache may answer frequent requests without consulting the primary database. Each additional layer improves some quality attribute but introduces possible failure modes and consistency questions.

1.1 Relational data models

The relational model represents data in relations, commonly implemented as tables. A table consists of rows, called tuples, and columns, called attributes. Each row represents one entity or relationship occurrence. A primary key uniquely identifies a row, while a foreign key refers to a key in another table.

For example, an online store may contain:

• Customer(customer_id, name, email)
• Product(product_id, name, price, stock_quantity)
• Order(order_id, customer_id, order_date, status)
• OrderItem(order_id, product_id, quantity, unit_price)

OrderItem resolves the many-to-many relationship between orders and products. Its composite primary key may be (order_id, product_id).

Relational systems are particularly effective when data has stable structure, relationships are important, and operations require strong transactional guarantees. SQL provides declarative selection, joining, grouping, aggregation, insertion, modification, and deletion.

1.2 NoSQL data models

NoSQL means “not only SQL,” rather than “without SQL.” NoSQL systems commonly sacrifice some relational features or transactional scope to achieve flexible schemas, high horizontal scalability, or specialized access patterns.

A document database might store an order as one document containing its line items. This improves retrieval of an entire order but may duplicate product descriptions and complicate updates. NoSQL design is therefore usually driven by known access patterns: model data according to the queries the system must perform.

NoSQL does not automatically mean faster, and relational databases are not automatically unsuitable for scale. The correct choice depends on workload, consistency requirements, query relationships, operational expertise, and cost.

2. Schema Design and Integrity

A schema describes the structure, constraints, relationships, and permitted values of stored data. Good design reduces ambiguity and prevents invalid states before they reach business logic.

2.1 Keys and constraints

Important relational constraints include:

• Primary key: uniquely identifies each row and is not null.
• Foreign key: requires a referenced value to exist, unless nullability is explicitly allowed.
• Unique constraint: prevents duplicate values, such as duplicate email addresses.
• Not-null constraint: requires a value.
• Check constraint: restricts values, such as quantity > 0.
• Default: supplies a value when one is not provided.

Constraints are valuable because validation in application code alone is vulnerable to race conditions, maintenance errors, and access by other applications.

2.2 Normalization

Normalization organizes relational data to reduce unnecessary redundancy and update anomalies. In simplified terms:

• First normal form (1NF): attributes contain atomic values and repeating groups are removed.
• Second normal form (2NF): 1NF holds and every non-key attribute depends on the whole candidate key, relevant especially to composite keys.
• Third normal form (3NF): 2NF holds and non-key attributes do not depend transitively on other non-key attributes.

Suppose an order table contains order_id, customer_id, customer_name, and order_date. customer_name depends on customer_id, not directly on the order. Storing it there can create inconsistent names when a customer changes their name. Separating customers and orders removes this transitive dependency.

Normalization is not an absolute command to eliminate all duplication. Deliberate denormalization can improve read performance, especially in reporting systems or document stores. The duplicated value must then have an explicit ownership and update strategy.

2.3 Schema evolution

Web applications change continuously. Safe schema evolution commonly follows an additive sequence:

1. Add a nullable column or new table.
2. Deploy application code that can read both old and new representations.
3. Backfill existing records in controlled batches.
4. Begin writing the new representation.
5. Verify completeness and remove obsolete structures later.

This expand-and-contract approach avoids requiring every application instance to change simultaneously. Destructive changes should be delayed until old readers and writers are no longer active.

3. SQL Querying and Relational Operations

SQL is declarative: the developer describes the desired result, while the query optimizer chooses an execution plan.

A representative query is:

SELECT c.customer_id, c.name, SUM(oi.quantity * oi.unit_price) AS total_spend
FROM Customer AS c
JOIN OrderTable AS o ON o.customer_id = c.customer_id
JOIN OrderItem AS oi ON oi.order_id = o.order_id
WHERE o.status = 'PAID'
GROUP BY c.customer_id, c.name
HAVING SUM(oi.quantity * oi.unit_price) > 1000
ORDER BY total_spend DESC;

The query filters paid orders, joins related rows, computes line totals, groups by customer, filters groups using HAVING, and sorts the result. The WHERE clause filters rows before grouping; HAVING filters groups after aggregation.

Parameterized queries should be used instead of string concatenation:

SELECT customer_id, name FROM Customer WHERE email = ?;

The parameter is bound by the database driver. This protects against SQL injection and often permits statement-plan reuse.

3.1 Joins

An inner join returns matching rows from both inputs. A left outer join preserves every row from the left input and supplies nulls where no matching right row exists. Join correctness depends on matching the intended keys. Joining on a non-unique or incorrect attribute can multiply rows and produce inaccurate totals.

3.2 Query plans and cost

A DBMS may use table scans, index scans, nested-loop joins, hash joins, or merge joins. EXPLAIN or an equivalent facility reveals the selected plan. Developers should examine estimated and, where available, actual row counts, access methods, join order, and expensive sorts or temporary operations.

A query that is fast for 1,000 rows may be unacceptable for 100 million rows. Performance testing should use representative data volume and realistic concurrency.

4. Indexing for Performance

An index is an auxiliary data structure that helps locate rows without scanning the entire table. A common B-tree index supports equality, range, and ordered queries. Hash indexes, inverted indexes, spatial indexes, and specialized structures serve other workloads.

Indexes improve reads but consume storage and impose work on inserts, updates, and deletes. Therefore, every index should be justified by a real access pattern.

For a query such as:

SELECT * FROM OrderTable
WHERE customer_id = ? AND order_date >= ?
ORDER BY order_date DESC;

an index beginning with (customer_id, order_date) may be useful. In a composite index, column order matters. The leading columns should generally reflect common equality predicates, followed by range or ordering columns, subject to the DBMS optimizer and workload.

An index is covering when it contains all values needed by a query, potentially avoiding table lookups. However, wide covering indexes increase storage and write cost. Low-cardinality columns, such as a Boolean status, may provide limited benefit when indexed alone.

Index selection should consider:

1. Frequent and slow queries.
2. Selectivity and expected result size.
3. Filtering, joining, and ordering columns.
4. Write frequency and maintenance cost.
5. Data distribution and skew.
6. Actual execution plans.

5. Transactions and Concurrency

A transaction is a logical unit of work that should be processed according to the ACID properties:

• Atomicity: all operations occur, or none are committed.
• Consistency: database constraints and declared invariants remain satisfied.
• Isolation: concurrent transactions do not produce unacceptable interference.
• Durability: committed data survives appropriate failures.

Consider transferring 500 currency units from account A to account B. The debit and credit must belong to one transaction:

BEGIN;
UPDATE Account SET balance = balance - 500 WHERE account_id = 'A';
UPDATE Account SET balance = balance + 500 WHERE account_id = 'B';
COMMIT;

If a validation fails, the transaction must be rolled back. In production, the application should also verify that exactly the intended rows were updated and should handle deadlocks through bounded retry logic.

5.1 Isolation levels

Isolation levels balance correctness and concurrency. Names and exact behavior vary by DBMS, but common levels are:

• Read uncommitted: may permit dirty reads; rarely suitable for critical data.
• Read committed: prevents reading uncommitted changes; common default.
• Repeatable read: stronger stability for rows read during a transaction, although implementation details differ.
• Serializable: provides behavior equivalent to some serial execution, generally with greater blocking or abort risk.

Typical anomalies include dirty reads, non-repeatable reads, and phantom reads. Snapshot or multiversion concurrency control can allow readers to see a consistent version while writers proceed, but it does not eliminate all conflicts.

5.2 Optimistic and pessimistic control

Optimistic concurrency assumes conflicts are uncommon. A row may include a version number:

UPDATE Product
SET stock_quantity = stock_quantity - ?, version = version + 1
WHERE product_id = ? AND version = ? AND stock_quantity >= ?;

If zero rows are affected, another process changed the record or insufficient stock existed. The application then retries or reports failure. Pessimistic locking instead locks records before modification, reducing some conflicts while potentially increasing contention and deadlocks.

Distributed transactions across independent databases are difficult and expensive. Where possible, use one transactional boundary, an outbox pattern, or compensating actions rather than assuming that several local commits form one atomic operation.

6. Object–Relational Mapping

Object–relational mapping maps application objects and relationships to relational tables. An ORM commonly provides identity tracking, query construction, schema migration facilities, and transaction integration.

ORMs improve productivity by reducing repetitive data-access code and allowing domain concepts to be expressed in familiar programming constructs. They can also introduce the object–relational impedance mismatch: objects support inheritance, identity, and navigation, whereas relational tables use rows, keys, and set-based operations.

A major performance problem is the N+1 query problem. The application first retrieves N orders, then executes one additional query for each order’s customer or items. A single request therefore issues N + 1 queries. Eager loading, explicit joins, batching, or carefully designed projections can reduce this cost.

ORM best practices include:

• Inspect generated SQL rather than assuming it is efficient.
• Use projections when complete entities are unnecessary.
• Define relationship-loading behavior explicitly.
• Keep transactions around business units of work.
• Avoid exposing persistent entities directly as public API contracts.
• Use database constraints in addition to model validation.
• Measure query count and latency in tests.

ORMs are tools, not substitutes for data modeling or SQL knowledge. Complex analytics may be clearer and faster in hand-written SQL.

7. Caching and Data Consistency

A cache stores reusable data closer to the application or user. Common forms include browser caches, content-delivery networks, reverse proxies, in-process caches, and distributed caches such as key–value stores.

The principal cache strategies are:

• Cache-aside: application checks the cache, reads the database on a miss, then populates the cache. Writes usually update the database and invalidate or refresh the cache.
• Read-through: the cache itself loads missing data from the backing store.
• Write-through: writes go through the cache to the persistent store.
• Write-behind: the cache acknowledges writes before asynchronously persisting them; this improves latency but increases durability risk.

A cache entry normally has a time-to-live (TTL). A shorter TTL reduces staleness but increases database load; a longer TTL improves hit rate but may expose older values. Cache invalidation is difficult because every update path must be considered.

Useful cache protections include request coalescing, randomized TTLs to prevent synchronized expiration, bounded size, eviction policies, and protection against caching sensitive data under shared keys. Cache failures should not normally cause data loss; the application needs a degraded but safe behavior when the cache is unavailable.

Consistency describes how observed values relate to committed writes. Strong consistency is valuable for balances, permissions, and inventory decisions. Eventual consistency may be acceptable for counters, search indexes, feeds, and recommendation data. A system may combine both: transactional storage for orders and asynchronously updated read models for dashboards.

A practical consistency design specifies:

1. The authoritative source of truth.
2. Which operations require atomicity.
3. The maximum acceptable staleness.
4. How updates propagate.
5. How conflicts are detected and resolved.
6. How repair and reconciliation occur.

8. A Comparative Design Example

Suppose a university develops a course-registration platform. Students search courses, view schedules, register, withdraw, and receive notifications. Course capacity must not be exceeded.

A relational database is appropriate for students, courses, sections, registrations, and prerequisites because these entities have strong relationships and registration requires transactional integrity. A document store could support flexible course descriptions and content metadata. A cache could hold frequently viewed course details. A search engine might provide full-text search, but it should not become the authoritative source for registration capacity.

A registration transaction can:

1. Lock or conditionally update the section’s available capacity.
2. Verify prerequisites and schedule conflicts.
3. Insert the registration row.
4. Commit all changes together.
5. Publish a registration event through an outbox record.

The outbox record is committed in the same database transaction. A worker later publishes it to a message broker and marks it as sent. If publication fails, the worker retries. Consumers should be idempotent, meaning repeated delivery does not create duplicate effects.

This design separates the authoritative registration decision from asynchronous notification and search updates. It also makes failure behavior explicit: a delayed email is acceptable, but two students receiving the same final seat is not.

9. Illustrative Case Study: Online Retail Order Processing

Background

This illustrative case concerns a retailer whose monolithic application uses a relational database for products, customers, orders, and payments. During a major sale, customers report slow checkout, duplicate orders, and inventory values that disagree with the product page.

Problem analysis

Investigation reveals three causes. First, the checkout page loads each product’s details with a separate ORM query, creating an N+1 pattern. Second, product pages use a long-lived cache, but inventory updates do not invalidate it. Third, the application creates an order before payment confirmation and retries the entire request after a timeout, producing duplicates.

Approach and solution

The team applies the following design:

• Replace repeated ORM loading with a batched query and a purpose-built projection.
• Add an index on the actual checkout lookup columns after inspecting execution plans.
• Keep inventory and order creation in a carefully bounded transaction.
• Use a conditional inventory update so stock cannot become negative.
• Add an idempotency key supplied by the client for payment and order requests.
• Store the idempotency result with a unique constraint.
• Invalidate or update relevant cache entries after committed inventory changes.
• Publish order events through a transactional outbox.
• Add metrics for query latency, cache hit ratio, transaction retries, and duplicate-request rejection.

Outcome

The revised system reduces unnecessary queries, prevents repeated requests from creating multiple orders, and makes inventory decisions against authoritative transactional data. Product descriptions may remain cached, but stock availability is either read from a short-lived representation with clear labeling or checked authoritatively during checkout.

Learning points

Performance problems often arise from interactions between layers rather than from one defective component. Caching cannot repair incorrect transaction design, and an ORM cannot guarantee efficient queries. Idempotency, constraints, observability, and explicit ownership of data are central to dependable web applications.

10. Security, Governance, and Professional Practice

Data systems process personal, financial, educational, and sometimes health information. Security must therefore be designed into persistence rather than added after implementation.

Important practices include:

• Apply least-privilege database accounts.
• Separate read, write, migration, and administrative credentials.
• Encrypt data in transit and protect sensitive data at rest.
• Store passwords using a modern adaptive password-hashing algorithm; never store plaintext passwords.
• Parameterize queries and validate authorization at the service boundary.
• Minimize collection and retention of personal data.
• Maintain audit records for sensitive actions without logging secrets.
• Test backup restoration, not merely backup creation.
• Classify data and define retention and deletion procedures.
• Consider applicable privacy and data-protection laws, contractual duties, and institutional policy.

Anonymization and pseudonymization reduce risk but are not identical. Pseudonymized data may still be linked to an individual using additional information. AI systems also require careful data management: training data may contain personal information, model outputs may leak sensitive content, and automated decisions require traceability, access controls, and evaluation for bias. AI should not be granted unrestricted direct write access to production databases.

11. Contemporary Developments and Limitations

Cloud-managed databases reduce operational burden through automated backups, replicas, scaling, and monitoring, but they do not remove the need for architecture and cost control. Serverless applications often use connection pools, proxies, or data APIs because creating a new database connection per invocation can exhaust database resources.

Distributed databases and globally replicated systems offer availability and geographic performance, but replication lag, conflict resolution, partition behavior, and operational complexity must be understood. Polyglot persistence—using more than one database technology—can be appropriate when each technology has a clearly defined responsibility. It can also increase failure modes, skills requirements, backup complexity, and consistency problems.

No database can optimize every workload simultaneously. A design should document its invariants, access patterns, failure assumptions, recovery objectives, and scaling limits. Decisions should be validated with measurements and failure testing rather than fashionable technology labels.

Best-Practice Checklist

1. Identify authoritative data owners and business invariants.
2. Design schemas from domain rules and access patterns.
3. Enforce critical integrity in the database.
4. Use parameterized queries.
5. Keep transactions short and explicit.
6. Inspect query plans and measure realistic workloads.
7. Add indexes selectively and revisit them as data changes.
8. Watch for ORM-generated N+1 queries.
9. Define cache invalidation and staleness behavior.
10. Make retries safe through idempotency.
11. Use backups, restoration tests, monitoring, and audit trails.
12. Plan additive schema migrations and rollback procedures.
13. Apply least privilege and protect personal information.
14. Document consistency guarantees and recovery expectations.

Chapter Summary

Data persistence is the foundation on which reliable web applications are built. Relational databases provide structured schemas, relationships, SQL, constraints, and mature transaction support. NoSQL systems provide alternative models suited to flexible documents, simple key lookups, large distributed workloads, or graph relationships. Neither category is universally superior.

Effective design begins with domain entities, invariants, access patterns, and failure requirements. Normalization reduces anomalies, while carefully controlled denormalization can improve read performance. Indexes accelerate selected access paths but add storage and write cost. Transactions provide atomicity and durability, while isolation and concurrency-control techniques determine how competing requests interact.

ORMs improve development productivity but must be evaluated through their generated queries and loading behavior. Caches reduce latency and load but introduce staleness and invalidation challenges. Strong consistency is appropriate for critical decisions such as payment and inventory; eventual consistency can support secondary views such as search and analytics. Security, privacy, backup recovery, observability, idempotency, and schema evolution are essential professional responsibilities.

Key Terms

Persistence: Retention of data beyond the lifetime of an application process.

DBMS: Software that stores, queries, protects, and manages databases.

Relational model: A model that represents data as related tables of rows and columns.

NoSQL: A family of non-relational or distributed data models, including document and key–value systems.

Schema: A description of data structures, relationships, types, and constraints.

Primary key: Attribute or set of attributes that uniquely identifies a row.

Foreign key: Attribute that references a key in another table.

Normalization: Organization of relational data to reduce redundancy and anomalies.

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

Index: Auxiliary structure that accelerates data retrieval.

Transaction: A logical unit of database work.

ACID: Atomicity, consistency, isolation, and durability properties.

Isolation level: The degree to which concurrent transactions are separated.

ORM: Software that maps application objects to relational data.

N+1 query problem: Inefficient execution of one query followed by a separate query for each returned item.

Cache-aside: A strategy in which the application loads data into the cache after a miss.

TTL: Time-to-live; the period before a cached item expires.

Eventual consistency: A guarantee that replicas converge if updates cease, without immediate uniform visibility.

Idempotency: Property whereby repeating an operation has the same intended effect as performing it once.

Outbox pattern: A technique that stores an event with the business transaction before asynchronous publication.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. State the four ACID properties.
3. Why can an index improve read performance but reduce write performance?
4. What is the N+1 query problem?
5. Distinguish strong consistency from eventual consistency.

Long Answer Questions

1. Compare relational and document databases for an e-commerce order system. Discuss structure, relationships, transactions, and scalability.
2. Explain normalization through third normal form and describe a situation where denormalization may be justified.
3. Describe common transaction isolation levels and the anomalies they are intended to control.
4. Explain cache-aside, write-through, and write-behind strategies, including their risks.
5. Discuss how an ORM can improve productivity while creating performance and design problems.

Analytical / Application Questions

1. Design a persistence architecture for a course-registration system. Identify authoritative data, transaction boundaries, indexes, cacheable data, and asynchronous operations.
2. A checkout request may be retried after a network timeout. Design an idempotency strategy that prevents duplicate orders and explain the required database constraint.
3. A query filtering by customer_id, restricting order_date to a range, and sorting by date is slow. Propose an indexing investigation, including the evidence you would collect before and after changing the schema.

Multiple Choice Questions with Answers

Question 1

Which property ensures that either all operations in a transaction occur or none of them do?

A. Atomicity

B. Isolation

C. Durability

D. Availability

Correct Answer: A. Atomicity

Question 2

Which database model is generally most suitable for traversing many connected relationships such as social networks?

A. Key–value

B. Graph

C. Document

D. Wide-column

Correct Answer: B. Graph

Question 3

What is the main purpose of a foreign key?

A. To sort rows automatically

B. To encrypt a column

C. To reference a key in another table

D. To create a cache entry

Correct Answer: C. To reference a key in another table

Question 4

Which query feature filters groups after aggregation?

A. WHERE

B. FROM

C. JOIN

D. HAVING

Correct Answer: D. HAVING

Question 5

In a cache-aside strategy, what normally happens after a cache miss?

A. The application reads the backing store and populates the cache

B. The database is permanently deleted

C. The cache rejects all future requests

D. The application writes only to the cache

Correct Answer: A. The application reads the backing store and populates the cache

Question 6

What is a common symptom of the ORM N+1 query problem?

A. One query is executed for an entire database backup

B. One initial query is followed by one query for each returned item

C. All queries are executed without parameters

D. A transaction always becomes serializable

Correct Answer: B. One initial query is followed by one query for each returned item

Question 7

Which practice most directly protects a SQL query from injection caused by user input?

A. String concatenation

B. Disabling indexes

C. Parameterized queries

D. Increasing cache TTL

Correct Answer: C. Parameterized queries

Question 8

Why can a long-lived product cache cause incorrect inventory displays?

A. It may serve stale values after inventory changes

B. It automatically normalizes all tables

C. It prevents all database transactions

D. It converts relational data into graph data

Correct Answer: A. It may serve stale values after inventory changes

Question 9

What does idempotency mean in request processing?

A. A request must always be asynchronous

B. Repeating an operation has the same intended effect as performing it once

C. Every request must update two databases

D. A cache entry never expires

Correct Answer: B. Repeating an operation has the same intended effect as performing it once

Question 10

Which technique stores an event in the same transaction as a business change before publishing it asynchronously?

A. Lazy loading

B. Read uncommitted isolation

C. Outbox pattern

D. Table scanning

Correct Answer: C. Outbox pattern