Data Modeling and Persistence

Introduction

Most full-stack applications are built around information that must survive beyond a single request. User accounts, products, orders, payments, messages, and permissions must be stored reliably, retrieved efficiently, and changed safely. Persistence is the process of maintaining data beyond the lifetime of a program or request. Data modeling is the disciplined design of the structures, relationships, rules, and access patterns used to represent that data.

A poor data model creates duplicated information, inconsistent records, slow queries, and difficult changes. A sound model supports application behaviour, protects data integrity, and provides a stable foundation for APIs and user interfaces. This chapter introduces relational databases and SQL, explains when NoSQL systems are appropriate, and develops practical skills in CRUD operations, constraints, indexes, transactions, migrations, and ORM or query-layer tools.

Learning Objectives

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

• identify entities, attributes, relationships, and business rules;
• design a basic relational schema using keys and normalization principles;
• write SQL for create, read, update, and delete operations;
• explain constraints, indexes, transactions, and isolation;
• distinguish relational and NoSQL use cases;
• use migrations and ORM or query-builder tools responsibly;
• apply persistence practices to a full-stack application.

1. From Application Requirements to a Data Model

A data model translates domain concepts into structures that a database can store. The design process normally begins with requirements rather than with tables or a preferred technology.

An entity is a significant object about which the system stores information, such as Customer, Product, or Order. An attribute describes an entity, such as a customer's email address. A relationship describes how entities are associated. For example, a customer places many orders, while each order belongs to one customer.

A useful modeling sequence is:

1. identify important nouns in the requirements;
2. identify the facts that must be stored about each noun;
3. identify relationships and their cardinalities;
4. identify rules that must always be true;
5. identify common queries and update operations;
6. select an appropriate persistence technology;
7. test the model against realistic data and workflows.

Cardinality describes the number of instances that can participate in a relationship:

• one-to-one: one employee has one account;
• one-to-many: one customer places many orders;
• many-to-many: students enroll in many courses and each course has many students.

In a relational database, a many-to-many relationship is represented by an associative table. For example, enrollment(student_id, course_id, enrolled_at) connects students and courses.

Relational Schema Example

Consider an online shop. A minimal relational design could contain:

orders.customer_id is a foreign key referencing customers.customer_id. order_items resolves the many-to-many relationship between orders and products. Storing unit_price in order_items is important: the price at purchase time may differ from the product's current price.

2. Relational Databases and Normalization

A relational database represents data as relations, commonly displayed as tables. Each row represents a record and each column represents an attribute. SQL is used to define, query, and manipulate these relations.

A primary key uniquely identifies each row. It may be a generated integer, UUID, or a meaningful natural value. Generated keys are often practical because business attributes such as email addresses can change.

A foreign key references a key in another table and expresses a relationship. Referential integrity prevents a child record from referring to a nonexistent parent.

Normalization

Normalization reduces unnecessary duplication and update anomalies. At certificate level, the most useful principles are:

• store one fact in one appropriate place;
• avoid repeating groups in a row;
• use atomic values rather than comma-separated lists;
• represent relationships with keys;
• separate entities that have independent lifecycles.

For example, placing customer_name in every order duplicates the name. If the customer changes their name, many rows may require updating. Storing customer_id in orders and the name in customers avoids this duplication.

Normalization is not an absolute command to split every table. Deliberate denormalization may improve read performance, reporting, or search, but duplicated values then require a synchronization strategy. Denormalization should be justified by measured access requirements rather than convenience.

Data Types and Nullability

Column types should represent valid values accurately. Common types include integers, decimal numbers, strings, dates, timestamps, Boolean values, and binary data. Monetary values should generally use a fixed-precision decimal type or integer minor units, not floating-point arithmetic, because floating-point representation can introduce rounding errors.

NULL means that a value is absent, unknown, or not applicable; it is not the same as zero or an empty string. Comparisons with NULL use three-valued logic. For example, the correct test is email IS NULL, not email = NULL. Excessive nullable columns often indicate an unclear model, although optional business data legitimately requires nullability.

3. SQL and CRUD Operations

CRUD describes the four basic data operations: Create, Read, Update, and Delete.

Defining Tables

The following SQL is illustrative; exact syntax varies slightly across PostgreSQL, MySQL, SQLite, and other systems.

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

CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku VARCHAR(40) NOT NULL UNIQUE,
    name VARCHAR(160) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

A CHECK constraint rejects values that violate a stated condition. A UNIQUE constraint prevents duplicate values. NOT NULL requires a value. These rules belong in the database as well as in application validation because other applications, scripts, or future code may write to the database.

Create and Read

INSERT INTO customers (email, name)
VALUES ('[email protected]', 'Sam Lee');

SELECT customer_id, email, name
FROM customers
WHERE email = '[email protected]';

Parameterized queries should be used instead of string concatenation. A query layer might send the SQL text and a separate parameter value. This prevents SQL injection, in which malicious input changes the meaning of a query.

A join combines related rows:

SELECT o.order_id, c.email, o.status, o.created_at
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE c.email = '[email protected]'
ORDER BY o.created_at DESC;

INNER JOIN returns matching rows. LEFT JOIN retains rows from the left table even when no matching child exists. Filtering, projection, ordering, grouping, and aggregation should be performed deliberately, with attention to result size.

Update and Delete

UPDATE orders
SET status = 'paid'
WHERE order_id = 101 AND status = 'pending';

DELETE FROM customers
WHERE customer_id = 25;

An UPDATE or DELETE without an appropriate WHERE clause can affect every row. Production code should check the number of affected rows and should consider authorization before modifying data. Foreign-key actions such as ON DELETE RESTRICT, CASCADE, or SET NULL must reflect business meaning. Cascading deletion is dangerous when records are legally or operationally required.

4. Constraints, Indexes, and Query Performance

Constraints are executable statements of data integrity. Primary keys, foreign keys, uniqueness, nullability, and checks protect the database from invalid state.

An index is an auxiliary data structure that helps the database locate rows without scanning the entire table. For example:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

This may help queries that filter by customer_id and sort by created_at. A composite index is ordered by its leading columns; an index on (customer_id, created_at) is generally more useful for customer-based access than for a query filtering only by created_at.

Indexes have costs. They consume storage and slow inserts, updates, and deletes because index entries must also be maintained. Indexes should be based on actual query patterns. Query plans, available through tools such as EXPLAIN, help developers understand whether an index is used and how many rows are estimated or scanned.

Common performance problems include selecting unnecessary columns, retrieving thousands of rows when pagination is needed, issuing one query per returned object (the N+1 query problem), and applying functions or mismatched types that prevent efficient index use. Keyset pagination, such as “rows after this last identifier,” can be more stable than large offset pagination for changing datasets.

5. Transactions and Consistency

A transaction groups operations into one logical unit. It should satisfy ACID properties:

• Atomicity: all operations succeed or none are committed;
• Consistency: constraints and defined rules remain satisfied;
• Isolation: concurrent transactions do not produce unacceptable interference;
• Durability: committed data survives ordinary failures.

A bank-like transfer illustrates atomicity. The debit and credit must not be committed separately:

BEGIN;

UPDATE accounts
SET balance = balance - 50
WHERE account_id = 1 AND balance >= 50;

-- Application verifies that one row was updated.

UPDATE accounts
SET balance = balance + 50
WHERE account_id = 2;

COMMIT;

If the balance condition fails, the application must issue ROLLBACK and report failure. In real systems, authorization, idempotency, currency, and concurrency must also be handled.

Isolation levels regulate concurrency. Weaker isolation can provide greater throughput but may allow phenomena such as dirty reads, non-repeatable reads, or phantom rows. Stronger isolation reduces these anomalies but may increase locking, waiting, or serialization failures. Applications must handle deadlocks and retry safe transactions where appropriate. A transaction should be short and should not wait for network calls while holding database locks.

6. NoSQL Databases and Appropriate Use

NoSQL is a broad family rather than one database design. Important categories include:

NoSQL systems may offer flexible schemas, horizontal scaling, locality of related data, or specialized relationship traversal. They may be effective when access patterns are known and the data naturally fits a document or graph model.

A document database could store an order with embedded item documents, making a complete order easy to read. However, embedding can duplicate product information, create large documents, and complicate updates. Referencing separate documents may improve reuse but requires multiple reads. NoSQL does not eliminate modeling; it often makes query and partition design even more important.

Relational databases are usually a strong default when data has complex relationships, strict integrity requirements, joins, and multi-record transactions. NoSQL should be selected for a clear requirement such as scale characteristics, flexible aggregate structure, or a specialized access pattern—not simply because it is newer.

7. Migrations and Schema Evolution

A migration is a versioned, repeatable change to database structure or controlled data. Migrations allow a team to reconstruct environments consistently and review schema changes alongside application code.

A safe expansion-and-contraction approach is:

1. add a new nullable column or table;
2. deploy code that can read both old and new forms;
3. backfill existing rows in manageable batches;
4. begin writing the new form;
5. verify completeness and correctness;
6. enforce the new constraint;
7. remove the old form in a later migration.

For example, renaming a column in one step can break old application instances during a rolling deployment. A compatibility period reduces this risk. Large migrations require monitoring, backups, lock analysis, and a rollback or recovery plan. A migration should be tested against a production-like dataset, not merely an empty development database.

8. ORMs, Query Builders, and Data Access Layers

An Object-Relational Mapper (ORM) maps application objects or models to database records. A query builder constructs SQL through a programming-language API. A lower-level data-access library executes parameterized SQL directly.

ORMs can reduce repetitive CRUD code, provide relationship loading, validation hooks, and migration support. Their risks include hidden queries, inefficient eager or lazy loading, abstraction leakage, and difficulty expressing advanced database features. A query builder offers more control but still requires SQL understanding. Direct SQL provides maximum transparency but increases repetitive code and maintenance responsibility.

Good practice includes:

• inspect generated SQL for important operations;
• use explicit transactions for multi-step changes;
• avoid mass assignment of security-sensitive fields;
• validate input at the application boundary and enforce integrity in the database;
• define loading strategies to avoid N+1 queries;
• test constraints, failures, and concurrency;
• keep persistence code separate from controllers and presentation logic.

The data-access layer should translate database errors into appropriate application responses without exposing internal schema details or sensitive diagnostics.

9. Applied Case: Course Registration Platform

The following is an illustrative case. A training provider is building a platform where learners browse courses, register, and receive completion records.

Background and Problem

An initial spreadsheet stores learner names, course names, instructor names, and registration dates in one row. The same course and learner information is repeated many times. Staff report inconsistent spelling, accidental duplicate registrations, and difficulty determining whether a course is full.

Analysis

The core entities are Learner, Course, Instructor, and Registration. A learner can register for many courses, and a course can contain many learners. Therefore, Registration is an associative entity. Business rules include:

• learner email must be unique;
• a learner cannot register twice for the same course;
• a course has a maximum capacity;
• registration status must be controlled;
• a registration references existing learner and course records.

A suitable schema includes:

CREATE TABLE registrations (
    learner_id BIGINT NOT NULL REFERENCES learners(learner_id),
    course_id BIGINT NOT NULL REFERENCES courses(course_id),
    status VARCHAR(20) NOT NULL CHECK (status IN ('active', 'withdrawn', 'completed')),
    registered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (learner_id, course_id)
);

The composite primary key prevents duplicate registrations. A separate index on (course_id, status) supports finding active registrations for a course. When checking capacity, the application should use a transaction so that two simultaneous registrations cannot both observe an available final place and exceed capacity. Depending on the database, the transaction may lock the course row or use a serializable operation and retry conflicts.

Outcome and Learning Points

The normalized model removes repeated facts, constraints protect important rules, and indexes support expected searches. Migrations can introduce the new tables while the old system remains operational. The case demonstrates that data modeling is not merely an implementation detail: it expresses policy, controls concurrency, and affects user experience.

10. Security, Governance, and Reliability

Persistent data often includes personal, financial, or educational information. Developers should collect only necessary data, restrict access by role, encrypt network connections, protect credentials, and avoid storing passwords in plaintext. Passwords must be processed using a modern adaptive password-hashing algorithm with a unique salt.

Audit requirements may require recording who changed important records and when. Retention and deletion policies should reflect legal duties and business needs. Backups must be encrypted, tested, and accompanied by a recovery procedure; an untested backup is not evidence of recoverability. Applications should use least-privilege database accounts and separate administrative credentials from normal runtime credentials.

Artificial intelligence may assist with schema suggestions, query drafting, or anomaly detection, but generated SQL must be reviewed, tested, and checked for privacy leakage. Confidential production data should not be submitted to an external model without authorization. Human accountability remains essential for destructive queries, migration decisions, and access controls.

Challenges and Limitations

No single data model optimizes every requirement. Strong consistency can reduce availability or throughput under some distributed designs. Flexible schemas can accelerate early development but shift validation responsibility to application code. Normalization reduces duplication but may require joins; denormalization improves selected reads but increases synchronization risk. ORMs improve productivity but can obscure performance. Transactions protect business operations but can create contention when poorly designed.

The appropriate design is therefore evidence-based. Document assumptions, identify important access patterns, measure performance, test failure modes, and revisit decisions as requirements evolve.

Best-Practice Checklist

• Model domain entities and relationships before choosing tables.
• Use primary keys, foreign keys, and constraints to protect integrity.
• Parameterize all externally influenced SQL values.
• Choose data types and nullability deliberately.
• Add indexes for measured access patterns, not every column.
• Use transactions for logically atomic operations.
• Keep transactions short and handle deadlocks or serialization failures.
• Version schema changes with tested migrations.
• Understand the SQL generated by an ORM.
• Protect, audit, back up, and test recovery of sensitive data.

Chapter Summary

Data modeling converts application requirements into entities, attributes, relationships, and enforceable rules. Relational databases provide tables, keys, joins, constraints, and transactions that are particularly valuable for structured data and strong integrity requirements. SQL supports CRUD operations, while indexes improve selected access patterns at a maintenance cost. Transactions provide atomic and durable units of work, but concurrency and isolation must be designed carefully.

NoSQL databases offer document, key-value, wide-column, and graph approaches for particular scalability or data-shape requirements. Migrations manage schema evolution safely, and ORMs or query layers can improve productivity when developers remain aware of generated queries and database behaviour. Secure persistence requires parameterized queries, least privilege, privacy protection, tested backups, and responsible governance. A high-quality model balances correctness, performance, maintainability, and the application's real access patterns.

Key Terms

Entity: A significant domain object represented in stored data.

Attribute: A property describing an entity.

Relationship: An association between entities.

Cardinality: The number of instances participating in a relationship.

Primary key: A value or set of values uniquely identifying a row.

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

Constraint: A database rule that restricts invalid data.

Normalization: Organizing data to reduce unnecessary duplication and anomalies.

Denormalization: Deliberately duplicating data to support selected performance or access needs.

CRUD: Create, Read, Update, and Delete operations.

Index: An auxiliary structure that can accelerate data retrieval.

Transaction: A logical unit of database work.

Atomicity: The transaction property that all operations succeed or none do.

Isolation: Control over the effects of concurrent transactions.

Migration: A versioned change to database structure or controlled data.

ORM: A tool that maps application objects or models to database records.

NoSQL: A family of non-relational database approaches.

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

SQL injection: An attack that manipulates dynamically constructed SQL.

Idempotency: The property that repeating an operation produces the intended safe result rather than unintended duplicates.

Review Questions

Short Answer Questions

1. What is the purpose of a foreign key?
2. Why is NULL different from an empty string or zero?
3. State the four ACID properties.
4. What problem does an index address, and what cost does it introduce?
5. Why are parameterized queries important?

Long Answer Questions

1. Explain how an online shop's many-to-many relationship between orders and products can be modeled relationally.
2. Compare normalization and denormalization, including one benefit and one risk of each.
3. Explain how a transaction protects a multi-step business operation.
4. Compare relational and document databases for a course registration platform.
5. Describe an expansion-and-contraction strategy for a breaking schema change.

Analytical / Application Questions

1. Design tables and key constraints for a library in which members borrow books, and explain how you would prevent duplicate active loans.
2. A page displays 500 customers and executes one query for each customer's orders. Identify the likely performance problem and propose two improvements.
3. Two users attempt to claim the final place in a course simultaneously. Explain how a transaction and an appropriate concurrency strategy can prevent over-enrollment.

Multiple Choice Questions with Answers

Question 1

Which database feature ensures that a child row refers to an existing parent row?

A. Foreign key

B. Index

C. View

D. Trigger

Correct Answer: A. Foreign key

Question 2

Which operation represents the 'U' in CRUD?

A. Undo

B. Update

C. Upload

D. Unlink

Correct Answer: B. Update

Question 3

What is the principal purpose of a database transaction?

A. To guarantee that a query uses an index

B. To group related operations into a controlled unit of work

C. To convert SQL into JSON

D. To remove all database constraints

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

Question 4

Which constraint prevents two customers from having the same email value?

A. CHECK

B. DEFAULT

C. UNIQUE

D. FOREIGN KEY

Correct Answer: C. UNIQUE

Question 5

Why should application code use parameterized SQL?

A. It prevents SQL injection by separating code from values

B. It automatically creates database backups

C. It removes the need for authorization

D. It guarantees every query is faster

Correct Answer: A. It prevents SQL injection by separating code from values

Question 6

A learner can register for many courses, and each course can have many learners. Which relational design is most appropriate?

A. Put all course names in one learner column

B. Create an associative registration table

C. Store all learners in the course name field

D. Use one table with no keys

Correct Answer: B. Create an associative registration table

Question 7

What is a likely disadvantage of adding many indexes?

A. Indexes prevent all SELECT queries

B. Indexes increase storage and can slow writes

C. Indexes remove referential integrity

D. Indexes make primary keys optional

Correct Answer: B. Indexes increase storage and can slow writes

Question 8

Which statement about a migration is correct?

A. It is a versioned change to database structure or data

B. It is a user-interface color scheme

C. It is always a database backup

D. It replaces all application tests

Correct Answer: A. It is a versioned change to database structure or data

Question 9

Which NoSQL type is naturally suited to representing nodes and relationships?

A. Key-value

B. Document

C. Graph

D. Wide-column

Correct Answer: C. Graph

Question 10

What should an application normally do if a transaction cannot complete safely?

A. Commit only the first successful statement

B. Rollback and report or safely retry the operation

C. Disable all constraints permanently

D. Delete the database index

Correct Answer: B. Rollback and report or safely retry the operation