4. Data Modelling, Persistence and Transactions

Introduction and Context

Backend systems do more than receive requests and return responses. They preserve facts about users, orders, payments, permissions, inventory, and business events. The quality of this persistent data determines whether an application remains reliable when usage grows, failures occur, requirements change, or multiple users act simultaneously. Data modelling therefore occupies a central position in backend engineering.

A data model translates business concepts into structures that software can store and retrieve. Persistence mechanisms implement that model, while transactions protect it when several operations must succeed or fail as a unit. These concerns are closely related. A poorly designed schema can produce duplication and inconsistency; an unsuitable index can make a correct query unusably slow; and an incorrectly bounded transaction can create lost updates, deadlocks, or partially completed business operations.

This chapter examines relational modelling, normalization, indexing, query planning, ACID transactions, isolation levels, locking, and schema migrations. It also compares ORM approaches in Node.js, Django, and Spring Boot, and evaluates NoSQL and polyglot persistence as architectural choices rather than fashionable replacements for relational databases.

Learning Objectives

After studying this chapter, the learner should be able to:

• model business requirements using entities, relationships, keys, constraints, and normalization;
• select and design indexes while considering query patterns and write cost;
• interpret query plans and identify common performance problems;
• explain ACID properties, isolation levels, locks, deadlocks, and optimistic concurrency;
• implement persistence with Sequelize, Prisma, TypeORM, Django ORM, and Spring Data JPA/Hibernate;
• design safe database migrations and deployment practices;
• evaluate relational, NoSQL, and polyglot persistence strategies;
• diagnose consistency, performance, governance, and operational risks in data-intensive systems.

From Business Facts to a Relational Model

A relational model represents data as relations, commonly implemented as tables. Each row represents an occurrence of an entity or relationship, and each column represents an attribute. A primary key identifies a row; foreign keys express references to rows in other tables.

Consider an online learning platform. Its domain may contain Student, Course, Module, Enrollment, Assignment, Submission, and Payment. A student can enroll in many courses, and a course can contain many students. This many-to-many relationship is represented by an associative table such as Enrollment(student_id, course_id, enrolled_at, status). Attributes belonging to the relationship—such as enrollment date or completion status—belong in Enrollment rather than in Student or Course.

A sound model distinguishes entities from values. Student is an entity with an identity; a postal address may be treated as an embedded value if it has no independent lifecycle. The decision depends on business meaning, reuse, update requirements, and audit needs. A model should also represent cardinality and optionality. For example, an Order must belong to one Customer, whereas a Customer may have no Orders. These rules should be reflected through NOT NULL constraints, unique constraints, foreign keys, and application validation where appropriate.

Keys and Integrity Constraints

A candidate key is any minimal attribute set that uniquely identifies a row. One candidate key is selected as the primary key. Surrogate keys, such as generated integers or UUIDs, are often convenient because they remain stable even if business attributes change. Natural keys, such as a government-issued identifier or an ISBN, carry business meaning but may be difficult to change or may expose sensitive information. A practical design may use a surrogate primary key while enforcing a unique constraint on the natural identifier.

Referential integrity ensures that a foreign-key value refers to an existing parent row, unless nullability explicitly permits absence. Delete behavior must be chosen carefully. CASCADE may be suitable for dependent line items that have no meaning without an order, but dangerous for records that must be retained for legal or analytical reasons. RESTRICT or soft deletion may be preferable for customers, invoices, or audit records.

Other constraints include CHECK conditions, NOT NULL, UNIQUE, and domain-specific rules. Constraints are valuable because they protect data regardless of which application or administrative tool writes it. Application checks remain useful for complex rules, but they should not be the only defence against invariant violations.

Normalization and Deliberate Denormalization

Normalization is a method for organizing relational data to reduce redundancy and update anomalies. First Normal Form requires atomic values and no repeating groups. Second Normal Form requires that every non-key attribute depend on the whole key, which matters especially for composite keys. Third Normal Form removes transitive dependencies, so a non-key attribute does not depend on another non-key attribute. Boyce–Codd Normal Form applies a stricter condition: every determinant must be a candidate key.

Suppose an OrderLine table stores order_id, product_id, product_name, quantity, and unit_price. Product_name depends on product_id, not on the entire order-line identity. Repeating product_name across many rows creates an update anomaly: changing the product name requires multiple updates. A normalized design stores product details in Product and records the price actually charged in OrderLine, because historical sale price is a property of the transaction rather than the current product catalogue.

Normalization does not mean that every database must be maximally decomposed. Joins have costs, and reporting workloads may benefit from carefully controlled denormalization. A summary table, materialized view, cached document, or duplicated read model can improve performance when its refresh and correctness rules are explicit. Denormalization should be justified by measured workload requirements, not by an assumption that joins are inherently bad. The duplicated data must have an owner, synchronization strategy, and acceptable staleness boundary.

Persistence Choices in Contemporary Backend Frameworks

Object-relational mapping (ORM) translates between application objects and relational rows. It improves developer productivity, manages parameter binding, supports associations, and can coordinate transactions. However, it does not eliminate the need to understand SQL. ORMs may generate inefficient joins, hide excessive queries, or make important database constraints invisible in code.

In Node.js, Sequelize provides models, associations, migrations, and transaction APIs. Prisma uses a schema-driven client and offers strongly typed queries; its explicit relation and nested-write features can simplify common operations. TypeORM maps classes and decorators to database structures and supports repositories, query builders, and transactions. In all three approaches, teams should inspect generated SQL for important paths, avoid loading unnecessary columns, and define indexes and constraints deliberately.

Django ORM places models and relationships close to the application domain. QuerySets are lazy: constructing a QuerySet does not necessarily execute a query until evaluation. This permits composition but can also obscure when database access occurs. select_related is useful for following foreign-key or one-to-one relationships through a SQL join, while prefetch_related performs additional queries for collections and combines results in memory. Django migrations record schema changes and should be reviewed rather than blindly accepted.

Spring Data JPA provides repository abstractions over JPA, commonly implemented by Hibernate. Entity mappings, fetch strategies, cascading, persistence contexts, JPQL, native SQL, and transaction annotations are central concepts. The persistence context can provide identity management and dirty checking, but careless use may produce the N+1 query problem or unexpectedly large sessions. Lazy loading is generally useful, yet accessing lazy associations outside a suitable transaction can cause failures or encourage the anti-pattern of keeping a database session open throughout a web response.

Across frameworks, a repository abstraction should not conceal transaction boundaries or business invariants. A service layer commonly defines the unit of work, while repositories handle persistence operations. Complex queries may appropriately use hand-written SQL, database views, or specialized data-access components.

Indexes and the Logic of Query Planning

An index is an auxiliary data structure that helps the database locate rows without scanning the entire table. B-tree indexes are effective for equality, range, and ordered searches. Hash indexes may be useful for equality in systems that support them, while full-text, spatial, and specialized indexes address other workloads.

Index design begins with observed queries, not with columns selected arbitrarily. If a system frequently searches active orders for a customer and sorts them by creation time, a composite index such as (customer_id, status, created_at) may be useful, subject to the database optimizer and actual selectivity. The order of columns matters. A composite index is generally most useful for predicates on its leading columns, although optimizer behavior varies by database engine.

Indexes accelerate reads but impose storage, memory, insertion, update, and deletion costs. An index on a low-cardinality column, such as a Boolean status, may provide little benefit unless combined with other selective predicates or implemented as a filtered/partial index. Covering indexes can reduce table access by including all columns needed by a query, but they should be used selectively.

A query planner estimates alternative execution strategies using statistics. An execution plan may reveal sequential scans, index scans, nested-loop joins, hash joins, sort operations, estimated versus actual row counts, and expensive filters. A sequential scan is not automatically a defect: for a small table or a query returning most rows, scanning may be cheaper than using an index. Diagnosis requires examining execution time, returned rows, data distribution, and workload frequency.

Common causes of poor plans include stale statistics, functions applied to indexed columns, implicit type conversion, leading wildcards in pattern searches, unselective predicates, excessive result sets, and queries that retrieve related records one at a time. Pagination should be designed carefully. Offset pagination is simple but becomes expensive at large offsets; keyset pagination uses a stable ordering and a continuation value, such as the last seen creation timestamp and identifier.

Transactions: Preserving Business Invariants

A transaction groups database operations into a unit of work. The familiar ACID model describes four properties. Atomicity means that the unit is committed completely or rolled back. Consistency means that committed transactions preserve declared and business constraints. Isolation limits the effects of concurrent transactions on one another. Durability means that committed data survives ordinary system failure through mechanisms such as logging and stable storage.

ACID does not guarantee that an application is logically correct. A transaction can atomically commit an incorrect price if its business rules are wrong. Nor does consistency in ACID mean that every replica is immediately identical; it refers primarily to valid state transitions under the database’s transaction model.

A transfer between two accounts illustrates transaction boundaries. The debit, credit, and creation of a transfer record should generally occur in one transaction. If the debit commits but the credit fails, money has effectively disappeared. External actions, such as sending an email or calling a payment provider, should not be treated as ordinary reversible database operations. Patterns such as an outbox table, idempotency keys, and compensating actions coordinate database state with external systems.

Isolation Levels and Anomalies

Isolation levels balance correctness against concurrency. Read Uncommitted may permit dirty reads. Read Committed prevents reading uncommitted data but may allow non-repeatable reads, where the same query returns different committed values during one transaction. Repeatable Read provides stronger stability, though exact behavior differs by database. Serializable aims to produce results equivalent to some serial execution, using locks, validation, or serialization mechanisms, but may reduce throughput or cause retries.

A phantom occurs when a repeated range query observes newly inserted or deleted rows matching its predicate. Lost updates occur when concurrent read-modify-write operations overwrite one another. Write skew can occur when two transactions independently observe a valid condition and make changes that are jointly invalid. Choosing an isolation level requires identifying the invariant being protected rather than selecting the strongest setting automatically.

Locking and Concurrency Control

Pessimistic concurrency control acquires locks before conflicting work proceeds. A SELECT ... FOR UPDATE can lock selected rows for later modification, but it must be used with a clear transaction boundary and appropriate index support. Locks held for too long reduce concurrency and increase deadlock risk. A deadlock occurs when transactions wait cyclically for resources. Databases usually detect deadlocks and abort one transaction; applications should safely retry appropriate transactions with bounded backoff.

Optimistic concurrency assumes conflicts are uncommon. A row may contain a version number or updated_at value. An update includes the previously read version in its predicate; if zero rows are affected, another transaction changed the row and the application can reject or reconcile the update. Optimistic control is often suitable for editing forms and APIs where holding locks during user interaction would be inappropriate.

Idempotency is a complementary design principle. An idempotency key allows a retried request—such as a payment submission—to produce one logical result rather than duplicate effects. The key and resulting response should be stored under a uniqueness constraint within a transaction.

Migrations as Controlled Change

A migration is a versioned, repeatable description of a schema change. It may create a table, add a nullable column, build an index, or modify a constraint. Migrations provide a shared history for development, testing, deployment, and rollback planning. They are not a substitute for backups, monitoring, or data migration design.

Safe schema evolution often follows an expand-and-contract process. First, add a compatible structure, such as a nullable column or new table. Next, deploy application code that can read old and new forms and backfill data in batches. Then begin writing the new form, validate completeness, and finally enforce constraints or remove the obsolete structure in a later release. This avoids requiring old application instances and new database structures to change simultaneously.

Large index creation may require online or concurrently supported operations. Adding a NOT NULL constraint to a large table may need validation before enforcement. Data backfills should be restartable, observable, throttled, and designed to avoid long locks. Migration files should be immutable after deployment; a correction is normally represented by a new migration.

Relational, NoSQL, and Polyglot Persistence

Relational databases are strong choices when relationships, constraints, transactions, ad hoc queries, and mature reporting are important. Document databases store aggregates as documents and can reduce joins when data is naturally accessed together. Key-value stores offer very fast access through known keys. Wide-column systems support high-volume distributed workloads with access patterns designed in advance. Graph databases are useful when traversing relationships is the primary operation.

NoSQL does not mean “no consistency” or “no modelling.” Each NoSQL system has a data model, consistency choices, partitioning strategy, and operational constraints. Document duplication may improve reads but creates update and versioning challenges. A distributed store may offer eventual consistency, requiring the product design to tolerate stale reads or provide reconciliation.

Polyglot persistence uses more than one storage technology in a system. For example, a relational database may own orders and payments, Redis may support short-lived caching, object storage may hold files, and a search engine may serve text queries. The central governance question is which system is authoritative for each fact. Replication pipelines, outbox events, reconciliation jobs, and explicit freshness guarantees are necessary to control divergence. Adding a second database increases operational complexity, backup requirements, security surfaces, observability needs, and failure modes.

Illustrative Case: Preventing Duplicate Orders in a Distributed Retail API

Background

An online retailer exposes a mobile and web checkout API. Customers occasionally retry requests because of slow networks. The initial implementation creates an order, decreases inventory, and requests payment through separate operations. During traffic peaks, duplicate orders and oversold products are reported.

Situation and Analysis

The order service first checks whether an order with the same client-generated request identifier exists. Two concurrent requests can both observe no existing order and proceed. Inventory is read and then decremented without a protected transaction, producing a lost-update problem. Payment calls are also retried without an idempotency key.

The team maps the invariants: each client request key may produce only one order; an order must contain immutable line-item prices; inventory cannot become negative; and payment attempts must be traceable. These are stronger requirements than merely making individual SQL statements succeed.

Approach

The relational schema adds a unique constraint on (customer_id, idempotency_key). Order and OrderLine are written in one transaction. Inventory rows are updated with a guarded statement such as “set available = available - quantity where product_id = ? and available >= quantity,” and the affected-row count is checked. For operations requiring inspection before update, rows are locked in a consistent product order to reduce deadlocks.

The transaction also writes an outbox event describing the order. A worker publishes the event and calls the payment provider using a provider-supported idempotency key. The API returns the stored result when the same request key is received again. A reconciliation process compares payment records, orders, and outbox delivery status.

Outcome

Duplicate submissions become one logical order, inventory updates are protected by database constraints and conditional updates, and external payment retries no longer create duplicate charges. The system accepts that event delivery may be delayed, so the user interface displays a pending payment state rather than claiming immediate completion.

Learning Points

The case demonstrates that correctness emerges from schema constraints, transaction design, concurrency control, idempotency, and operational recovery together. No ORM setting alone can solve a distributed business invariant. It also shows why external side effects require an explicit coordination pattern.

Risks, Governance, and Professional Practice

Data persistence introduces risks beyond performance. Sensitive personal and financial data should be minimized, encrypted appropriately, access-controlled, and retained only as long as justified. Logs must avoid exposing credentials, tokens, and unnecessary personal data. Database accounts should follow least privilege, and production migrations should be auditable and approved through change management.

Regulatory requirements may affect deletion, retention, residency, auditability, and subject access. Soft deletion can support recovery but may conflict with genuine erasure obligations if copies remain in backups, caches, indexes, or analytical stores. Teams should document data ownership, lineage, classification, retention, and permitted uses.

Common technical mistakes include relying only on ORM validation, using floating-point arithmetic for monetary values, opening transactions around network calls, creating indexes without measuring workload, allowing unbounded queries, ignoring time zones, and assuming rollback can undo an external email or payment. Automated tests should include constraint tests, migration tests, concurrency tests, query-count assertions, failure injection, and restore exercises.

Current Developments and Best Practices

Modern data platforms increasingly combine transactional databases with event streams, search indexes, caches, and analytical warehouses. Change data capture can propagate committed changes, but consumers must handle ordering, duplication, schema evolution, and replay. Serverless and managed databases simplify infrastructure operations while making connection pooling, cold starts, quotas, and vendor-specific behavior important design considerations.

AI-assisted coding can generate models and queries quickly, but generated SQL and migrations require human review. AI systems may also expose sensitive data through prompts or logs, so governance and data classification remain essential. Automated query analysis and anomaly detection can help identify regressions, but metrics should be interpreted with domain context.

Effective practice includes the following:

• model invariants explicitly and enforce critical ones in the database;
• define transaction boundaries around business units of work;
• inspect execution plans for important and slow queries;
• use parameterized queries and least-privilege credentials;
• measure before adding indexes or denormalizing;
• make retries safe through idempotency and bounded backoff;
• keep migrations backward compatible during rolling deployments;
• monitor locks, deadlocks, latency, connection pools, replication lag, and storage;
• document the authoritative source and acceptable staleness of every data product.

Chapter Summary

Data modelling converts business concepts into entities, relationships, keys, constraints, and access structures. Normalization reduces redundancy and anomalies, while controlled denormalization can serve measured read-performance needs. ORM tools in Node.js, Django, and Spring Boot improve productivity but do not replace SQL knowledge, query-plan analysis, or database constraints.

Indexes must reflect real query patterns and are traded against write and storage costs. Transactions provide atomicity, durability, and controlled concurrency, but isolation levels, locks, optimistic versioning, idempotency, and retry behavior must be selected according to business invariants. Migrations should evolve schemas through compatible, observable stages. Relational and NoSQL systems offer different consistency, modelling, and scaling trade-offs; polyglot persistence is useful when ownership and synchronization are explicit. Secure governance, privacy, monitoring, testing, and recovery are integral parts of professional persistence engineering.

Key Terms

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

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

Normalization: Organizing relations to reduce redundancy and modification anomalies.

Denormalization: Deliberate duplication or restructuring of data for access or performance needs.

Candidate key: Minimal attribute set capable of uniquely identifying a row.

Referential integrity: Requirement that references point to valid related records.

Index: Auxiliary structure that accelerates selected data-access operations.

Composite index: Index containing multiple columns in a defined order.

Query plan: Database-selected strategy for executing a query.

ORM: Software layer that maps application objects to database structures.

Transaction: Atomic unit of database work.

Atomicity: Property that a transaction commits entirely or not at all.

Isolation: Degree to which concurrent transactions are separated.

Durability: Persistence of committed changes despite ordinary failure.

Dirty read: Reading data written by an uncommitted transaction.

Deadlock: Cyclic waiting among transactions holding conflicting locks.

Optimistic concurrency: Conflict-detection approach using versions or conditional updates.

Migration: Versioned change to database schema or data structure.

Idempotency: Property that repeating a request produces one logical effect.

Polyglot persistence: Use of multiple data-storage technologies in one system.

Review Questions

Short Answer Questions

1. What is the purpose of a foreign-key constraint?

2. Distinguish normalization from denormalization.

3. Name the four ACID properties.

4. What is an execution plan?

5. Why are migrations commonly made backward compatible?

Long Answer Questions

1. Explain first, second, and third normal forms using an order-processing example.

2. Discuss how composite indexes should be designed and evaluated.

3. Compare Read Committed, Repeatable Read, and Serializable isolation.

4. Explain how ORM abstractions can improve and harm backend persistence design.

5. Describe the expand-and-contract approach to schema evolution.

Analytical / Application Questions

1. Two users purchase the last item in stock simultaneously. Design a transaction and concurrency strategy that prevents negative inventory.

2. A Django endpoint performs one query for customers and one additional query for every customer’s orders. Diagnose the problem and propose an ORM-based solution.

3. An organization proposes replacing its relational order database with a document store solely to avoid joins. Evaluate the proposal using modelling, transaction, consistency, and operational criteria.

22. Multiple Choice Questions with Answers

Question 1

Which design best represents a many-to-many relationship between students and courses when enrollment date must be stored?

A. Store all course IDs in one Student column

B. Store all student IDs in one Course column

C. Create an Enrollment associative table with student_id, course_id, and enrolled_at

D. Duplicate student and course details in every enrollment record

Correct Answer: C. Create an Enrollment associative table with student_id, course_id, and enrolled_at

Question 2

Why should the price charged for an order line usually be stored in OrderLine rather than read only from Product?

A. Because Product tables cannot contain prices

B. Because the transaction must preserve the historical sale price

C. Because foreign keys cannot reference Product

D. Because indexes cannot be created on Product

Correct Answer: B. Because the transaction must preserve the historical sale price

Question 3

Which situation most directly illustrates a lost update?

A. A transaction reads uncommitted data

B. Two transactions read the same value and later overwrite each other’s changes

C. A query returns newly inserted rows on repetition

D. A committed transaction disappears after a restart

Correct Answer: B. Two transactions read the same value and later overwrite each other’s changes

Question 4

What is the principal purpose of a unique constraint on a client-provided idempotency key?

A. To make all queries use an index scan

B. To prevent repeated requests from producing duplicate logical effects

C. To encrypt the request body

D. To guarantee that external payment calls cannot fail

Correct Answer: B. To prevent repeated requests from producing duplicate logical effects

Question 5

Which statement about indexes is most accurate?

A. Every frequently queried column should have an independent index

B. Indexes always improve both reads and writes

C. Index usefulness depends on query patterns, selectivity, and maintenance cost

D. A sequential scan always indicates a faulty database design

Correct Answer: C. Index usefulness depends on query patterns, selectivity, and maintenance cost

Question 6

A transaction updates two account rows. Which practice can reduce deadlock risk when many transfers occur?

A. Hold locks while calling an external payment service

B. Update account rows in a consistent order

C. Remove all foreign keys

D. Use unbounded automatic retries

Correct Answer: B. Update account rows in a consistent order

Question 7

Which migration strategy is most suitable for a rolling deployment?

A. Drop the old column before deploying new application code

B. Add a compatible structure, deploy transitional code, backfill, then remove the old structure

C. Change the schema manually on each application server

D. Require every application instance to stop indefinitely

Correct Answer: B. Add a compatible structure, deploy transitional code, backfill, then remove the old structure

Question 8

In Django ORM, which approach is generally appropriate for loading a foreign-key relationship through a SQL join?

A. select_related

B. prefetch_related only

C. rollback_transaction

D. create_indexless

Correct Answer: A. select_related

Question 9

Which statement best describes optimistic concurrency control?

A. It holds a database lock while a user edits a form

B. It detects conflicts using a version or conditional update

C. It disables transaction isolation

D. It guarantees that conflicts never occur

Correct Answer: B. It detects conflicts using a version or conditional update

Question 10

What is the main governance question when using relational storage, Redis, and a search engine together?

A. Which system has the most tables

B. Which system is authoritative for each fact and how synchronization is managed

C. Which system has the shortest product name

D. Whether all systems can use identical query syntax

Correct Answer: B. Which system is authoritative for each fact and how synchronization is managed