Data Modeling and Persistence

Introduction

Backend applications are valuable only when they can preserve, retrieve, and protect information. Data modeling is the disciplined process of representing business concepts, relationships, rules, and history in a form that a database can store and query. Persistence is the broader concern of ensuring that data survives beyond the lifetime of a process and remains consistent under failure, concurrency, and change.

For full-stack developers, persistence is not merely a matter of writing SQL or calling an ORM method. A poorly designed model creates duplicate data, inconsistent updates, slow queries, difficult migrations, and security risks. A well-designed model expresses business rules clearly while supporting reliable application behavior. This chapter focuses primarily on relational persistence with Django ORM and Node.js data-access libraries, while also identifying situations in which a NoSQL database is appropriate.

Learning Objectives

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

• Translate business requirements into relational entities, attributes, and relationships.
• Apply primary keys, foreign keys, candidate keys, constraints, and normalization.
• Select indexes based on query patterns and understand their costs.
• Explain transactions, isolation, concurrency, and durability.
• Use migrations safely in Django and Node.js projects.
• Configure connection pooling and avoid common resource-management errors.
• Compare Django ORM with Node.js libraries such as Prisma, Sequelize, and Knex.
• Recognize appropriate and inappropriate uses of NoSQL databases.

1. From Domain Requirements to a Relational Schema

A database schema is a formal description of data structures and integrity rules. Design begins with the domain rather than with framework syntax.

Consider an online learning platform. Important domain concepts may include Student, Course, Instructor, Enrollment, Assignment, Submission, and Grade. A noun is not automatically an entity: “address” may be a separate entity if it is shared or independently managed, but it may simply be attributes of Student if it has no independent identity.

A practical modeling sequence is:

1. Identify entities and their business meaning.
2. List attributes and their data types.
3. Identify relationships and their cardinalities.
4. Select identifiers.
5. State business rules as constraints.
6. Test the model against representative queries and updates.

Relationships are commonly classified as one-to-one, one-to-many, or many-to-many. A student may have many enrollments, while each enrollment belongs to one student. Students and courses usually form a many-to-many relationship; an associative entity, Enrollment, converts this into two one-to-many relationships.

Figure: An associative entity resolves the many-to-many relationship between students and courses.

Keys and Integrity

A primary key uniquely identifies each row and must be unique and non-null. A foreign key references a key in another table and enforces referential integrity. A candidate key is any minimal attribute set that could uniquely identify a row; one candidate key is selected as the primary key. A natural key has business meaning, such as an email address or course code. A surrogate key, such as an auto-incrementing integer or UUID, has no business meaning.

Surrogate keys are convenient for relationships and allow business attributes to change, but they do not eliminate the need for unique constraints on natural identifiers. For example, a Student table might use student_id as its primary key while enforcing UNIQUE(email). A composite key may be appropriate for an associative table, although many teams use a surrogate enrollment_id plus a unique constraint on (student_id, course_id).

Common constraints include NOT NULL, UNIQUE, CHECK, DEFAULT, and FOREIGN KEY. Constraints should be enforced in the database, not only in application code, because multiple application instances, administrative tools, and background jobs may write to the same database.

2. Normalization and Controlled Denormalization

Normalization reduces redundancy and update anomalies. A relation is in first normal form when attributes contain atomic values appropriate to the domain and repeating groups are removed. Second normal form requires first normal form and that every non-key attribute depends on the whole candidate key, which matters especially for composite keys. Third normal form requires second normal form and removes transitive dependencies: non-key attributes should not depend on other non-key attributes.

Suppose an enrollment table contains:

With a composite key (student_id, course_id), student_name depends only on student_id and course_title only on course_id. This violates second normal form. A normalized design separates Student(student_id, student_name), Course(course_id, course_title), and Enrollment(student_id, course_id, grade).

Normalization prevents anomalies:

• Insert anomaly: a course cannot be recorded until a student enrolls.
• Update anomaly: a changed course title must be corrected in many rows.
• Delete anomaly: deleting the last enrollment may accidentally delete course information.

Normalization is not an absolute command to maximize the number of tables. Read-heavy systems may deliberately duplicate stable data, store a summary column, or use a materialized view. Denormalization should be explicit, justified by measured performance needs, and supported by a strategy for keeping copies consistent.

3. Data Types, Constraints, and Domain Accuracy

Choose types according to meaning, precision, and supported operations. Use date/time types rather than arbitrary strings. Store monetary values as fixed-precision decimal values or integer minor units, not binary floating-point values. If an amount is stored in cents, the stored integer 1250 represents $12.50 under the stated currency convention. The application must also store currency when multiple currencies are possible.

Boolean fields should have clear semantics, and nullable fields should mean “unknown or not applicable,” not merely “not yet validated.” A CHECK constraint can enforce a valid range, such as quantity ≥ 0, although database support varies by product and version.

A database constraint is a final integrity boundary; application validation provides better user feedback. Both layers are useful. For example, an application may check password policy and then rely on a unique database constraint for race-safe email uniqueness.

4. Indexing and Query Performance

An index is an additional data structure that helps the database locate rows without scanning the entire table. Indexes are valuable for columns used in selective WHERE predicates, joins, ordering, and uniqueness enforcement. They consume storage and make INSERT, UPDATE, and DELETE operations more expensive because index entries must also be maintained.

Index design should begin with actual query patterns. A composite index on (tenant_id, created_at) can support queries filtering by tenant and ordering or filtering by creation time. Column order matters: an index generally supports predicates on a leading prefix more effectively than predicates using only a later column. The query planner, data distribution, and database engine determine the precise behavior, so EXPLAIN or an equivalent plan tool should be used.

Avoid indexing every column. Low-cardinality fields, such as a boolean status, may provide little benefit alone. A foreign key is often worth indexing because it is commonly used in joins, although some systems do not automatically create such an index. Partial, covering, or expression indexes can be useful advanced features but reduce portability.

A sound workflow is: identify a slow query, inspect its execution plan, add or revise an index, benchmark representative data, and verify write overhead. Performance claims based only on a small development database are unreliable.

5. Transactions, Concurrency, and Isolation

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

• Atomicity: all operations commit or none do.
• Consistency: committed data satisfies declared rules and application invariants.
• Isolation: concurrent transactions observe behavior governed by an isolation level.
• Durability: committed data survives ordinary system failure according to the database’s guarantees.

A transfer illustrates atomicity. Debiting one account and crediting another must not be separated by a process failure. A transaction does not automatically make incorrect logic correct; it protects a correctly specified unit of work.

Isolation levels control phenomena such as dirty reads, non-repeatable reads, and phantom reads. Stronger isolation can reduce anomalies but may increase locking, waiting, or serialization failures. Exact behavior differs among database engines. Developers must understand the selected engine’s documentation rather than assuming that the ORM name alone determines semantics.

A common race condition is “check then insert.” Two requests may both observe that an email is unused and then attempt to insert it. A UNIQUE constraint is necessary; the application must handle the resulting conflict. For inventory, a transaction may lock the relevant row or use an atomic conditional update so that stock cannot become negative.

Keep transactions short, avoid network calls inside them, access shared resources in a consistent order, and implement bounded retry logic for transient deadlocks or serialization failures. Never blindly retry non-idempotent operations without an idempotency strategy.

6. Migrations and Schema Evolution

A migration is a versioned, executable change to database structure or sometimes data. It allows a team to reproduce schema state across development, testing, and production. Migrations should be committed to version control and reviewed like source code.

A safe production change often follows the expand-and-contract pattern:

1. Expand: add a nullable column or new table without breaking old code.
2. Deploy compatible application code that writes both old and new representations if required.
3. Backfill existing data in controlled batches.
4. Validate completeness and correctness.
5. Switch reads to the new representation.
6. Contract: remove obsolete columns or code in a later release.

Large table rewrites, lock-heavy operations, and unbounded backfills can cause outages. Test migration duration and locking behavior against production-like data. A migration must be safe to run exactly once through the migration system, and teams should understand whether a particular operation is transactional in their database engine.

Django ORM Example

from django.db import models

class Course(models.Model):
    code = models.CharField(max_length=20, unique=True)
    title = models.CharField(max_length=200)
    active = models.BooleanField(default=True)

class Enrollment(models.Model):
    student = models.ForeignKey("Student", on_delete=models.PROTECT)
    course = models.ForeignKey(Course, on_delete=models.PROTECT)
    enrolled_at = models.DateTimeField(auto_now_add=True)
    status = models.CharField(max_length=20, default="active")

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["student", "course"],
                name="one_enrollment_per_student_course"
            )
        ]
        indexes = [models.Index(fields=["course", "status"])]

After changing models, makemigrations creates migration files and migrate applies them. select_related() is useful for foreign-key joins, while prefetch_related() handles many-to-many or reverse relationships through additional queries. These tools help avoid the N+1 query problem, in which one initial query is followed by a separate query for each returned object.

A transaction can be expressed with transaction.atomic(). It defines a database transaction boundary, but the developer remains responsible for validation, conflict handling, and suitable isolation.

7. Node.js Data-Access Patterns

Node.js applications commonly use Prisma, Sequelize, TypeORM, Knex, or the pg driver for PostgreSQL. An ORM maps application objects or models to database tables and offers query construction, relationships, validation hooks, and migration tooling. A query builder offers more SQL control while reducing string-concatenation risks. A driver provides the most direct database access.

Prisma provides generated, typed clients and schema-driven migrations. Sequelize and TypeORM provide model-oriented abstractions. Knex is primarily a query builder and migration tool. No choice eliminates the need to understand SQL, indexes, transactions, and query plans.

Example using a generic PostgreSQL pool:

import pg from "pg";
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10
});

export async function createEnrollment(studentId, courseId) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await client.query(
      `INSERT INTO enrollment(student_id, course_id)
       VALUES ($1, $2)
       RETURNING enrollment_id, student_id, course_id`,
      [studentId, courseId]
    );
    await client.query("COMMIT");
    return result.rows[0];
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

Parameterized queries prevent values from being interpreted as SQL syntax. The finally block is essential: failing to release a pooled client eventually exhausts the pool. A transaction must use the same client for every statement; sending statements through unrelated pool calls can place them on different connections and therefore outside one transaction.

8. Connection Pooling and Operational Reliability

Opening a database connection has cost, and databases support a finite number of concurrent connections. A connection pool maintains a bounded set of reusable connections. Pool size should reflect database capacity, application instance count, query duration, and workload. If each of 20 application instances opens 20 connections, the potential total is 400 connections before administrative or background connections are counted.

A larger pool is not automatically faster. Excess concurrency can cause contention, memory pressure, and queueing in the database. Monitor acquisition wait time, active connections, idle connections, query duration, errors, and transaction duration. Configure connection timeouts and graceful shutdown so that a service stops accepting work before abandoning active database operations.

Use separate read replicas only when the application can tolerate replication lag and the consistency model is understood. Do not route a read immediately after a write to a lagging replica when read-your-write behavior is required.

9. ORM Patterns and Their Boundaries

The repository pattern can isolate persistence operations from business logic, although excessive abstraction may hide important query behavior. Unit-of-work patterns group changes and commit them as a unit. Data-transfer objects can prevent database models from being exposed directly through APIs.

Useful ORM practices include explicit eager loading, pagination, selecting only required columns, bulk operations where safe, and logging slow queries. Avoid exposing arbitrary client-provided field names or filters directly to an ORM query builder. Apply authorization before retrieving or modifying tenant-owned records; a correctly parameterized query can still return data belonging to the wrong user.

ORMs can obscure joins, locking, generated SQL, and transaction boundaries. Raw SQL is sometimes the appropriate choice for complex reports, database-specific features, or performance-critical operations. It should remain parameterized, reviewed, tested, and isolated rather than scattered throughout the codebase.

10. When NoSQL Is Appropriate

NoSQL is a family of approaches rather than one database design. Document databases store aggregate-shaped records, key-value stores optimize direct key access, wide-column systems support distributed high-volume access patterns, and graph databases represent relationships and traversals.

A document database may suit a catalog whose product attributes differ substantially by category, or a content system where an aggregate is usually read and written as one document. A key-value store may suit sessions, cache entries, or rate-limit counters. NoSQL is also considered when horizontal scale, flexible schema, or geographically distributed availability dominates relational joins and strong multi-record transactions.

NoSQL does not mean “without data modeling.” It often requires modeling around access patterns, deliberate denormalization, document-size limits, consistency choices, and explicit handling of duplicate data. A relational database is generally preferable when the domain has complex relationships, strong referential integrity, ad hoc joins, and multi-record transactional rules. Polyglot persistence can be useful, but every additional database increases operational, security, backup, and data-governance complexity.

11. Applied Case: Course Enrollment Service

Background

This illustrative case concerns a university enrollment service. Students enroll in courses through a web application, and administrators manage course capacity.

Problem

The initial implementation stored student names, course titles, and enrollment rows in one table. Duplicate enrollments occurred under concurrent requests, course titles became inconsistent, and listing a student’s courses generated hundreds of queries.

Analysis

The combined table contained partial dependencies and update anomalies. The service also performed a “check capacity, then insert” sequence without a transaction. Its database had no unique constraint and no index supporting the common query by student_id.

Approach and Solution

The team created Student, Course, and Enrollment tables. Enrollment received a unique constraint on (student_id, course_id), foreign keys, a status field, and an index on (student_id, status). The enrollment operation became transactional: lock or atomically update the course’s available capacity, insert the enrollment, and commit. Conflict errors were converted into meaningful API responses.

The API used eager loading for the student-course listing and paginated results. A migration first added the new tables, then backfilled records, verified duplicates, and finally removed the old table after a period of dual compatibility. The database pool was monitored and bounded per service instance.

Outcome

The design eliminated duplicate enrollments at the database boundary, made course and student information consistent, and reduced query count through deliberate loading. The team also gained a reproducible migration history and observable transaction behavior.

Learning Points

The case demonstrates that schema normalization, database constraints, transaction design, indexing, ORM query planning, and deployment strategy are interconnected. No single framework feature substitutes for a coherent persistence design.

12. Security, Ethics, and Governance

Persistence systems contain personal, financial, educational, and sometimes health information. Apply least privilege: application roles should have only the permissions required for their work. Protect credentials through secret management, encrypt connections, encrypt sensitive backups, and define retention and deletion policies.

Use parameterized queries and validate input, but do not treat validation as authorization. Enforce tenant isolation in every relevant query. Audit access to sensitive records without logging passwords, tokens, or unnecessary personal data. Consider privacy principles such as purpose limitation, data minimization, accuracy, retention limitation, and the rights applicable under laws such as the GDPR or local data-protection regulations.

Backups must be encrypted, tested through restoration exercises, and governed by recovery objectives. Soft deletion can support auditability but must not be used to evade legally required erasure. Data lineage and migration records are important for accountability, especially when automated or AI-assisted systems use stored data.

13. Contemporary Development and Best Practices

Modern teams increasingly use typed database clients, schema validation, managed databases, observability platforms, and automated migration checks in CI/CD. AI coding tools can generate model definitions or queries, but generated code must be reviewed for authorization errors, unsafe raw SQL, inefficient loading, incorrect migrations, and leakage of sensitive data into external services.

A professional persistence checklist includes:

• Model the domain and access patterns before choosing an ORM.
• Enforce critical invariants with database constraints.
• Normalize first; denormalize only for a measured reason.
• Design indexes from observed queries and inspect execution plans.
• Define transaction boundaries and handle conflicts explicitly.
• Use version-controlled, tested migrations.
• Parameterize queries and apply authorization consistently.
• Bound and monitor connection pools.
• Test backups, restoration, failure behavior, and migration rollback or recovery procedures.
• Document ownership, retention, privacy, and operational responsibilities.

Chapter Summary

Data modeling converts domain concepts into entities, relationships, keys, attributes, and constraints. Relational normalization reduces redundancy and anomalies, while carefully justified denormalization can improve read performance. Primary keys identify rows; foreign keys preserve relationships; unique and check constraints enforce invariants at the database boundary.

Indexes accelerate selected access paths but consume storage and write resources, so they must be designed from real query patterns. Transactions provide ACID behavior, but correct isolation, locking, conflict handling, and short transaction scope remain essential. Migrations make schema evolution reproducible, and expand-and-contract deployment reduces production risk. Django ORM and Node.js libraries improve productivity, yet developers must understand generated queries, loading strategies, pooling, and SQL boundaries. NoSQL systems are appropriate for particular access patterns and scale requirements, not as universal replacements for relational databases. Security, privacy, observability, backups, and governance are integral parts of persistence engineering.

Key Terms

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

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

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

Surrogate key: An identifier generated without direct business meaning.

Natural key: A business attribute or set of attributes used as an identifier.

Constraint: A database rule that restricts invalid data.

Normalization: Structuring relations to reduce redundancy and anomalies.

Denormalization: Deliberate duplication or precomputation for a justified purpose.

Index: An auxiliary structure that accelerates selected data access paths.

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

Transaction: A logical unit of database work.

Atomicity: The all-or-nothing property of a transaction.

Isolation level: A defined policy for what concurrent transactions can observe.

Migration: A versioned change to database schema or data.

ORM: Object-relational mapper connecting application models to relational data.

N+1 query problem: Excessive queries caused by fetching related data one record at a time.

Connection pool: A bounded collection of reusable database connections.

Referential integrity: Consistency of references between related tables.

NoSQL: A broad group of non-relational database approaches.

Idempotency: The property that repeating an operation has the intended single-effect outcome.

Review Questions

Short Answer Questions

1. What distinguishes a primary key from a foreign key?
2. Why can a many-to-many relationship require an associative entity?
3. State the four ACID properties.
4. Why should database connections be pooled and released?
5. What problem does the expand-and-contract migration pattern address?

Long Answer Questions

1. Explain first, second, and third normal form using an enrollment example.
2. Discuss how indexes improve performance and why excessive indexing is harmful.
3. Compare Django ORM, a Node.js ORM, a query builder, and a database driver.
4. Explain transaction isolation and give an example of a race condition.
5. Compare relational and NoSQL databases for a content or commerce application.

Analytical / Application Questions

1. Design a relational schema for a library system with members, books, copies, loans, and overdue charges. Specify keys, constraints, and at least three indexes.
2. An API checks inventory, waits for payment authorization, and then decrements stock in one long transaction. Analyze the risks and propose a safer design.
3. A query listing 10,000 students performs one query for students and one query for each student’s enrollments. Identify the problem, propose ORM-level remedies, and describe how you would verify improvement using query plans and measurements.

Multiple Choice Questions with Answers

Question 1

Which constraint prevents two enrollments for the same student and course?

A. A UNIQUE constraint on (student_id, course_id)

B. An index on course title only

C. A CHECK constraint on enrolled_at

D. A DEFAULT constraint on status

Correct Answer: A. A UNIQUE constraint on (student_id, course_id)

Question 2

Which normal form removes a non-key attribute that depends on only part of a composite key?

A. First normal form

B. Second normal form

C. Third normal form

D. Fourth normal form

Correct Answer: B. Second normal form

Question 3

Why are parameterized queries important?

A. They automatically create indexes

B. They prevent values from being interpreted as SQL syntax

C. They eliminate the need for authorization

D. They guarantee serializable transactions

Correct Answer: B. They prevent values from being interpreted as SQL syntax

Question 4

What is the main purpose of a database transaction?

A. To store all data in memory

B. To group related operations into a controlled logical unit

C. To replace foreign keys

D. To make every query faster

Correct Answer: B. To group related operations into a controlled logical unit

Question 5

Which practice helps prevent the N+1 query problem in Django?

A. Using select_related or prefetch_related appropriately

B. Removing all foreign keys

C. Disabling transactions

D. Creating a separate database for every user

Correct Answer: A. Using select_related or prefetch_related appropriately

Question 6

What is a major cost of adding indexes?

A. They prevent all SELECT queries

B. They increase storage and write-maintenance overhead

C. They remove primary keys

D. They guarantee that every query uses the index

Correct Answer: B. They increase storage and write-maintenance overhead

Question 7

In a Node.js PostgreSQL transaction using a pool, why must all statements use the same checked-out client?

A. A transaction is associated with one database connection

B. Pool clients cannot execute SELECT statements

C. Only one client can exist in a process

D. Different clients automatically share uncommitted data

Correct Answer: A. A transaction is associated with one database connection

Question 8

Which situation is generally a strong reason to use a relational database?

A. A cache with direct key lookup only

B. Complex relationships requiring referential integrity and multi-record transactions

C. Unstructured logs that are never queried

D. A temporary in-memory session object

Correct Answer: B. Complex relationships requiring referential integrity and multi-record transactions

Question 9

What does the expand-and-contract migration strategy begin with?

A. Deleting the old schema immediately

B. Adding compatible structures before removing obsolete ones

C. Disabling backups

D. Rebuilding every application instance simultaneously

Correct Answer: B. Adding compatible structures before removing obsolete ones

Question 10

Which statement about connection pools is correct?

A. A larger pool is always faster

B. Connections should be released after use

C. Pools eliminate database capacity limits

D. A transaction can safely switch clients between statements

Correct Answer: B. Connections should be released after use