Web Data Management

Introduction

Modern web applications are data-intensive systems. A university portal stores students, courses, enrolments, grades, and payments; an online retailer manages products, customers, orders, inventory, and recommendations; an AI service records training data, model versions, prompts, predictions, and audit events. Web data management concerns the design, storage, retrieval, protection, consistency, and eventual disposal of such information.

A sound data layer must do more than store records. It must represent relationships accurately, answer queries efficiently, remain available under failure, protect confidential information, and evolve as requirements change. This chapter examines relational and NoSQL databases, querying, transactions, indexes, object–relational mapping (ORM), caching, consistency, migration, and data lifecycle management.

Learning Objectives

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

1. Select relational or NoSQL storage for a given web application.

2. Design normalized relational schemas and suitable NoSQL data models.

3. Write representative SQL and understand document-oriented queries.

4. Explain transactions, isolation, consistency, and concurrency problems.

5. Design indexes and evaluate their costs.

6. Use ORM responsibly while understanding its limitations.

7. Integrate caching without creating unacceptable stale-data risks.

8. Plan migrations, retention, security, and deletion throughout the data lifecycle.

1. Data Management as an Architectural Responsibility

The data layer includes the database, data-access code, caches, migration tools, backup systems, and governance controls. Important design questions include:

• What entities and relationships exist?
• Which operations require strong consistency?
• What is the expected read/write volume and access pattern?
• How large can the data become, and how quickly will it grow?
• What availability, latency, privacy, and recovery objectives apply?

A database is not merely a permanent cache. It is usually the system of record: the authoritative source whose state can be reconstructed and audited. A cache is a performance layer containing copies that may be removed or become stale.

Data quality has several dimensions. Accuracy means that values represent reality; completeness means required information is present; consistency means related records do not contradict one another; timeliness concerns how current information is; and validity means that values conform to permitted formats and domains.

2. Relational Data Models

A relational database represents data in tables consisting of rows and columns. A relation has a schema, while a row represents an instance. A primary key uniquely identifies each row. A foreign key references a key in another table and expresses a relationship.

Consider a course registration system:

```sql

CREATE TABLE Student (

student_id BIGINT PRIMARY KEY,

name VARCHAR(120) NOT NULL,

email VARCHAR(255) NOT NULL UNIQUE

);

CREATE TABLE Course (

course_id BIGINT PRIMARY KEY,

code VARCHAR(20) NOT NULL UNIQUE,

title VARCHAR(200) NOT NULL

);

CREATE TABLE Enrollment (

student_id BIGINT NOT NULL REFERENCES Student(student_id),

course_id BIGINT NOT NULL REFERENCES Course(course_id),

enrolled_at TIMESTAMP NOT NULL,

grade VARCHAR(2),

PRIMARY KEY (student_id, course_id)

);

```

The composite key in `Enrollment` prevents the same student from being enrolled in the same course twice. A many-to-many relationship is represented by an associative table.

Normalization

Normalization reduces redundancy and update anomalies. In first normal form, attributes are atomic and repeating groups are separated. In second normal form, non-key attributes depend on the whole composite key, not merely part of it. In third normal form, non-key attributes depend on the key, the whole key, and nothing but the key.

For example, storing `(student_id, student_name, course_id, course_title, grade)` in one table repeats student and course data. Changing a course title requires many updates, and inserting a course without an enrolment may be difficult. Separating students, courses, and enrolments removes these anomalies.

Normalization is not an absolute rule. Analytical systems may deliberately denormalize data to reduce joins and improve read performance. Such duplication should be intentional, documented, and maintained by controlled processes.

SQL Queries

SQL is declarative: the programmer states what data is required rather than specifying every access step.

```sql

SELECT s.name, c.code, e.grade

FROM Student s

JOIN Enrollment e ON e.student_id = s.student_id

JOIN Course c ON c.course_id = e.course_id

WHERE s.student_id = :student_id

ORDER BY c.code;

```

Parameterized values such as `:student_id` are essential. Constructing SQL by concatenating untrusted input can cause SQL injection.

Aggregation supports analysis:

```sql

SELECT c.course_id, c.code, COUNT(e.student_id) AS enrolment_count

FROM Course c

LEFT JOIN Enrollment e ON e.course_id = c.course_id

GROUP BY c.course_id, c.code

HAVING COUNT(e.student_id) >= 30;

```

`LEFT JOIN` preserves courses with no enrolments; `HAVING` filters groups after aggregation. Query planners select execution strategies such as index scans, joins, and sorts. Developers should inspect execution plans for slow queries rather than assuming that an index will always help.

3. NoSQL Data Models

NoSQL is a broad category rather than one single technology. Common types include:

• Document databases: store JSON-like documents and are suitable for aggregate-oriented records.
• Key–value stores: retrieve a value by a key and are useful for sessions, feature flags, and caches.
• Wide-column databases: organize data by partition and clustering keys for very large distributed workloads.
• Graph databases: represent vertices and edges for highly connected data such as social or recommendation networks.

A document representation of a product might be:

```json

{

"productId": "P17",

"name": "Laptop",

"price": 899.00,

"variants": [

{"sku": "P17-B", "color": "black", "stock": 12},

{"sku": "P17-S", "color": "silver", "stock": 4}

]

}

```

Embedding variants is convenient when they are usually read with the product and have a manageable size. Referencing variants separately may be preferable when they are independently updated, shared, or unbounded.

NoSQL design commonly begins with access patterns. If the principal operation is “retrieve a user’s recent orders,” partitioning and document structure should make that operation efficient. Unlike normalized relational design, intentional duplication is common. The designer must consider partition-key distribution, document size, hot partitions, and consistency guarantees.

NoSQL does not automatically mean “no transactions” or “unstructured.” Many systems support constraints or transactions within defined scopes, and documents still require explicit validation and governance.

4. Transactions, Concurrency, and Consistency

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

• Atomicity: all operations occur, or none do.
• Consistency: declared rules remain satisfied before and after the transaction.
• Isolation: concurrent transactions do not produce prohibited interference.
• Durability: committed data survives an accepted failure model.

Suppose a payment order must be created and inventory reduced. A transaction can prevent an order from being recorded while the stock reduction fails. However, a transaction cannot automatically make an external email service or payment gateway atomic with the database. Patterns such as an outbox table and idempotent message processing are used for such distributed workflows.

Isolation and Concurrency

Weak isolation may produce dirty reads, non-repeatable reads, or phantom rows. Stronger isolation reduces these anomalies but can increase locking, waiting, or aborts. Exact isolation behavior depends on the database engine.

A practical inventory update is often expressed as a conditional statement:

```sql

UPDATE Product

SET stock = stock - :quantity

WHERE product_id = :id AND stock >= :quantity;

```

The application checks the affected-row count. One affected row means the condition succeeded; zero means insufficient stock or a missing product. This avoids a vulnerable “read, then write” sequence in which two requests both observe the same stock.

Optimistic concurrency uses a version value:

```sql

UPDATE Document

SET body = :body, version = version + 1

WHERE document_id = :id AND version = :old_version;

```

A zero-row result indicates that another update occurred. The application can ask the user to merge or retry. Retries must be bounded and operations should be idempotent where possible.

Distributed systems frequently trade between consistency, availability, and partition tolerance in practical designs. The important engineering task is not to repeat a slogan, but to identify which operations require immediate agreement and which can tolerate eventual convergence.

5. Indexes and Query Performance

An index is an auxiliary data structure that helps locate rows without scanning every record. B-tree indexes are broadly useful for equality, range, and ordered queries. Hash-style indexes may be useful for equality in systems that support them. Specialized structures such as full-text or spatial indexes address particular workloads.

For example:

```sql

CREATE INDEX idx_enrollment_course

ON Enrollment(course_id);

```

This can improve queries filtering or joining on `course_id`. A composite index must be designed around its column order. An index on `(student_id, enrolled_at)` is generally useful for filtering by `student_id` and ordering or filtering by `enrolled_at`; it may be less useful for a query filtering only by `enrolled_at`.

Indexes have costs: they consume storage, slow inserts and updates, and require maintenance. Low-selectivity columns, such as a boolean status in a balanced table, may not justify a standalone index. Index choice should follow measured query patterns and execution plans.

A query can fail to use an index when it applies a function to an indexed column, performs an incompatible type conversion, uses a leading wildcard, or returns a large fraction of the table. “Index everything” is therefore poor practice.

6. Object–Relational Mapping

ORM frameworks map application objects to relational tables. They provide identity mapping, change tracking, query construction, relationship handling, and transaction integration. They can improve productivity and reduce repetitive SQL.

However, an ORM does not eliminate database concepts. Developers must understand generated SQL, joins, cardinality, constraints, transactions, and indexes. A common defect is the N+1 query problem: one query loads a collection, followed by one query for each item. Eager loading, explicit joins, batching, or carefully designed projections can address it.

Other risks include accidental full-table updates, overly broad object graphs, hidden lazy-loading queries, and mismatch between object inheritance and relational structure. Production systems should log slow queries, inspect generated SQL, use parameter binding, and keep data-access boundaries explicit.

7. Caching and Consistency

Caching reduces latency and database load by reusing recently retrieved results. Common patterns include:

• Cache-aside: the application checks the cache, reads the database on a miss, and then populates the cache.
• Read-through: a cache layer obtains missing data on behalf of the application.
• Write-through: writes update the cache and backing store through a coordinated layer.
• Write-behind: writes are buffered and persisted later, which improves throughput but increases durability and consistency risks.

Cache-aside is common because it is simple, but invalidation is difficult. A cached product price may become stale after a database update. Time-to-live (TTL), explicit invalidation, versioned keys, and event-driven invalidation are possible controls.

Caching is appropriate for data that is expensive to compute, frequently read, and acceptable when briefly stale. It is dangerous for authorization decisions, account balances, or inventory unless the consistency model is explicit. Cache keys must include all relevant dimensions, and sensitive data must not be exposed across users or tenants.

8. Migrations and Schema Evolution

A migration changes database structure or data while preserving service continuity. A safe process includes version control, review, backups, testing on production-like data, observability, and a rollback or forward-repair plan.

A widely used expand-and-contract approach is:

1. Add a nullable or backward-compatible column.

2. Deploy application code that can read old and new representations.

3. Backfill existing rows in controlled batches.

4. Begin writing the new representation.

5. Verify completeness and correctness.

6. Switch reads to the new field.

7. Remove the old field only after dependent versions are retired.

Large migrations must consider locks, replication lag, transaction duration, failure recovery, and load impact. Data transformations need validation counts, checksums, or domain-specific reconciliation. A migration is not complete merely because the DDL command succeeds.

9. Data Lifecycle, Security, and Governance

The data lifecycle includes collection, validation, active use, sharing, archival, retention, and deletion. Retention should be based on legal, contractual, operational, and research requirements rather than indefinite accumulation.

Security controls include least-privilege database accounts, encryption in transit and at rest, secret management, parameterized queries, input validation, audit logs, backup protection, and separation of development from production data. Test environments should use masked or synthetic data where practical.

Privacy principles include purpose limitation, data minimization, transparency, access controls, correction procedures, and deletion or anonymization where required by applicable law. AI applications intensify these concerns because training data may contain personal information, sensitive attributes, or biased historical decisions. Dataset lineage, consent or lawful-use analysis, access logging, and reproducibility are important governance practices.

Backups require recovery testing. Two useful objectives are recovery point objective (RPO), the maximum acceptable data loss measured in time, and recovery time objective (RTO), the target time to restore service. These are operational objectives, not guarantees produced automatically by a backup command.

10. Illustrative Case: University Learning Platform

Background

A university learning platform stores student profiles, course materials, submissions, grades, and activity events. Its original relational database works well for enrolment and grading but experiences slow dashboards during examination periods.

Problem

The dashboard repeatedly calculates recent activity and course statistics. Large event tables increase query time, while indiscriminate caching risks showing outdated grades. A new analytics team also wants to store semi-structured model outputs.

Analysis

Student identity, enrolment, assessment, and grade data require strong constraints and clear relationships, so they remain relational. Activity events have high write volume and varied attributes; they are suitable for an append-oriented event store or partitioned relational table. Dashboard results are read frequently and can tolerate short staleness, whereas grades cannot be stale after an instructor publishes them.

Approach

The team adds indexes based on measured queries, including a composite index for course activity by time. It introduces a summary table refreshed incrementally from an outbox event stream. Dashboard summaries use a short TTL cache. Grade reads bypass that cache or invalidate the relevant key immediately after publication. Model outputs are stored as versioned documents linked to a student and assessment, with access controls and retention rules.

A migration adds event identifiers and timestamps using an expand-and-contract process. Batch backfill avoids long locks. Monitoring records query latency, cache hit rate, replication lag, failed jobs, and data-quality discrepancies.

Outcome and Learning Points

Dashboard latency improves without weakening grade consistency. The main lesson is that one database need not serve every workload identically, but each additional store creates synchronization, observability, security, and operational costs. Data modeling must begin with business invariants and access patterns, not fashionable technology.

Contemporary Developments and Best Practices

Cloud-managed databases provide automated backups, replicas, scaling, and monitoring, but responsibility for schema quality, access control, and cost remains with the engineering team. Serverless applications require attention to connection pooling and burst traffic. Change-data-capture pipelines support analytics and search indexes, but they introduce delivery ordering, duplication, and replay concerns.

Useful best practices are to:

1. Define ownership and the system of record for every important data item.

2. Model constraints in the database as well as in application code.

3. Design from real access patterns and measure with representative workloads.

4. Use parameterized queries and least-privilege credentials.

5. Make retries safe through idempotency keys or uniqueness constraints.

6. Treat caches and replicas as consistency decisions, not merely performance tools.

7. Test migrations, backups, restoration, and deletion procedures.

8. Monitor latency, errors, saturation, stale data, and data-quality indicators.

9. Document retention, lineage, and responsible access.

10. Review ORM-generated queries and avoid hiding important database behavior.

Chapter Summary

Web data management combines modeling, querying, reliability, performance, security, and governance. Relational databases provide schemas, joins, constraints, and mature transactions; NoSQL systems provide specialized models and scalability for particular access patterns. Normalization reduces anomalies, while deliberate denormalization can support measured read requirements. Transactions provide atomicity, consistency, isolation, and durability within their scope, but distributed workflows require additional patterns. Indexes accelerate selected queries at the cost of storage and write maintenance. ORMs improve productivity but do not replace database knowledge. Caches reduce latency while introducing staleness and invalidation problems. Safe migrations use compatibility, backfill, verification, and controlled removal. Finally, responsible systems manage data from collection through deletion, incorporating privacy, security, recovery, and AI governance.

Key Terms

System of record: Authoritative source for a data item.

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

Foreign key: Constraint linking a column to a key in another table.

Normalization: Structuring relations to reduce redundancy and anomalies.

Denormalization: Intentional duplication to improve access performance.

Document database: Database storing structured, JSON-like documents.

Transaction: Logical unit of database operations.

Atomicity: Property that a transaction fully succeeds or has no effect.

Isolation: Control of interference among concurrent transactions.

Durability: Persistence of committed data under the stated failure model.

Index: Auxiliary structure that accelerates selected data access.

Composite index: Index containing multiple ordered columns.

ORM: Software mapping application objects to relational data.

N+1 query problem: One initial query followed by many per-record queries.

Cache-aside: Pattern in which the application loads missing cache entries.

TTL: Time-to-live before cached data expires.

Migration: Controlled change to schema or stored data.

RPO: Maximum acceptable period of data loss.

RTO: Target time for service recovery.

Data minimization: Collecting and retaining only necessary data.

Review Questions

Short Answer Questions

1. What roles do primary keys and foreign keys play in a relational schema?

2. State the four ACID properties.

3. Why can an index improve reads but slow writes?

4. What is the N+1 query problem?

5. Distinguish a cache from a system of record.

Long Answer Questions

1. Compare relational and document databases for an online learning platform.

2. Explain normalization through third normal form and discuss when denormalization may be justified.

3. Describe transaction isolation and two concurrency-control techniques.

4. Explain cache-aside, including its benefits and consistency risks.

5. Present an expand-and-contract migration plan for renaming a production column.

Analytical / Application Questions

1. An inventory service reads stock, waits, and then writes a decremented value. Explain the race condition and propose a safer database operation.

2. A dashboard query filters by `course_id`, orders by `event_time`, and returns recent events. Propose an index and explain its column order and limitations.

3. A university wants to cache grades for one hour. Evaluate this design and recommend a consistency and invalidation strategy.

Multiple Choice Questions with Answers

Question 1

Which constraint prevents duplicate enrollment of the same student in the same course?

A. A composite primary key on student_id and course_id

B. A cache TTL

C. A document embedding rule

D. A foreign key on course title

Correct Answer: A. A composite primary key on student_id and course_id

Question 2

Which ACID property means that a committed transaction survives the stated failure model?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 3

Why is a parameterized SQL query preferred?

A. It automatically creates indexes

B. It reduces SQL injection risk

C. It removes the need for constraints

D. It guarantees zero latency

Correct Answer: B. It reduces SQL injection risk

Question 4

Which index is most directly aligned with filtering by student_id and ordering by enrolled_at?

A. (enrolled_at, student_id)

B. (student_id, enrolled_at)

C. (grade)

D. (course title)

Correct Answer: B. (student_id, enrolled_at)

Question 5

What characterizes the N+1 query problem?

A. One query is executed for every database table

B. A query returns exactly N plus one columns

C. One initial query is followed by one query per loaded item

D. A transaction is committed N+1 times atomically

Correct Answer: C. One initial query is followed by one query per loaded item

Question 6

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

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

B. The database is permanently replaced by the cache

C. All cached records are deleted

D. The request is always rejected

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

Question 7

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

A. To remove all historical data immediately

B. To allow schema evolution across compatible application versions

C. To disable database backups

D. To guarantee that no queries require testing

Correct Answer: B. To allow schema evolution across compatible application versions

Question 8

Which situation is generally a good reason to embed data in a document?

A. The embedded collection is unbounded and independently shared

B. The data is usually read with its parent and remains manageable in size

C. The data must always be normalized across many documents

D. The data has no defined access pattern

Correct Answer: B. The data is usually read with its parent and remains manageable in size

Question 9

What does RPO describe?

A. Target service restoration time

B. Maximum acceptable period of data loss

C. Number of replicas required

D. Maximum query response size

Correct Answer: B. Maximum acceptable period of data loss

Question 10

Which approach helps prevent a lost update when records contain a version value?

A. Update only when the stored version equals the version previously read

B. Disable all primary keys

C. Cache every write indefinitely

D. Use an unbounded retry loop

Correct Answer: A. Update only when the stored version equals the version previously read