Data Modelling and Persistence

Introduction

Most useful software systems must remember information after a program terminates. A banking application stores accounts and transactions; a learning platform stores learners, courses and assessments; an online shop stores products, orders and payments. Persistence is the durable storage and retrieval of application data. Data modelling is the disciplined process of representing the meaning, structure and relationships of that data.

For Java and Python developers, persistence is not simply a matter of writing SQL or calling an ORM method. A sound solution requires decisions about entities, keys, constraints, transactions, validation, schema evolution, security, performance and the suitability of relational or NoSQL storage. This chapter develops those decisions from relational foundations to practical JPA/Hibernate and SQLAlchemy implementations.

Learning Objectives

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

• identify entities, attributes, keys and relationships in a problem domain;
• design normalized relational schemas and express them in SQL;
• use constraints, validation and transactions to protect data integrity;
• explain migrations and apply them safely in a project;
• use JPA/Hibernate and SQLAlchemy for common persistence tasks;
• recognize ORM performance problems, especially the N+1 query problem;
• compare relational databases with appropriate NoSQL alternatives;
• apply security, privacy and governance practices to persistent data.

1. From Domain Concepts to Relational Models

A domain model describes important concepts in a business or organizational context. A university enrolment system may contain Learner, Module, Lecturer and Enrolment. A relational model represents these concepts using tables, rows and columns.

An entity is a distinguishable object about which data is stored. An attribute describes an entity. A relationship expresses how entities are associated. For example, a learner may enrol in many modules, while a module may contain many learners. This is a many-to-many relationship and normally requires an associative table such as Enrolment.

A relational table should have a primary key: a column or combination of columns that uniquely identifies each row. A foreign key references a key in another table. The following design separates entities and records the many-to-many relationship explicitly:

Learner(learner_id, name, email)
Module(module_id, code, title, credits)
Enrolment(learner_id, module_id, enrolled_on, grade)

Here, Enrolment.learner_id references Learner.learner_id, and Enrolment.module_id references Module.module_id. A composite primary key (learner_id, module_id) prevents the same learner from being enrolled in the same module twice. Alternatively, an artificial enrolment_id may be used, but a unique constraint on (learner_id, module_id) is still needed if duplicate enrolments are invalid.

Cardinality and Optionality

Cardinality describes how many instances may participate in a relationship:

A one-to-many relationship between Customer and Order places customer_id in Order. The order depends on an existing customer, so customer_id is generally NOT NULL.

2. Relational Integrity and Normalization

Relational design uses constraints to make invalid states difficult or impossible to store. Important constraints include:

• PRIMARY KEY: unique and non-null row identity;
• FOREIGN KEY: referential integrity between tables;
• UNIQUE: prevents duplicate values or combinations;
• NOT NULL: requires a value;
• CHECK: restricts values according to a predicate;
• DEFAULT: supplies a value when one is omitted.

Normalization reduces unnecessary duplication and update anomalies. In practical diploma-level design, the following forms are especially useful:

• First normal form (1NF): each column contains atomic values and repeating groups are separated;
• Second normal form (2NF): 1NF holds and every non-key attribute depends on the whole key, relevant mainly to composite keys;
• Third normal form (3NF): 2NF holds and non-key attributes do not depend on other non-key attributes.

Suppose an order table stores order_id, customer_id, customer_name, product_id, product_name, quantity and unit_price. Customer and product details are repeated for every order line. Updating a customer name requires multiple updates and may produce inconsistent values. A better model separates Customer, Product, Order and OrderLine. The price charged at the time of purchase may appropriately remain in OrderLine because it is a historical snapshot, not merely duplicated current product data.

Normalization is not an absolute rule. Controlled denormalization can improve read performance, but it introduces synchronization responsibilities. Such a decision should be based on measured workload and documented invariants rather than convenience.

3. SQL Data Definition and Manipulation

A relational database schema can be created using SQL Data Definition Language (DDL):

CREATE TABLE learner (
    learner_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE
);

CREATE TABLE module (
    module_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    title VARCHAR(160) NOT NULL,
    credits INTEGER NOT NULL CHECK (credits > 0)
);

CREATE TABLE enrolment (
    learner_id INTEGER NOT NULL,
    module_id INTEGER NOT NULL,
    enrolled_on DATE NOT NULL,
    grade DECIMAL(5,2),
    PRIMARY KEY (learner_id, module_id),
    FOREIGN KEY (learner_id) REFERENCES learner(learner_id),
    FOREIGN KEY (module_id) REFERENCES module(module_id),
    CHECK (grade IS NULL OR (grade >= 0 AND grade <= 100))
);

Exact identity syntax varies among database systems; PostgreSQL, MySQL and SQLite have different facilities. Production code should use the syntax supported by the selected database rather than assuming portability.

Data Manipulation Language (DML) includes INSERT, SELECT, UPDATE and DELETE. Parameterized queries are essential:

cursor.execute(
    "SELECT learner_id, name FROM learner WHERE email = ?",
    (email,)
)

The placeholder syntax differs by driver, but the principle is constant: values must be bound as parameters, not concatenated into SQL strings. This prevents SQL injection and handles quoting correctly.

Indexes improve lookup speed by maintaining an additional access structure, but they consume storage and slow writes. Index columns used frequently in filtering, joining or ordering, such as enrolment.module_id, should be considered. Index design should be validated with query plans and realistic data; indexing every column is not a best practice.

4. Transactions and Consistency

A transaction is a logical unit of work that should be completed entirely or not at all. The classic ACID properties are:

• Atomicity: all operations succeed or none take effect;
• Consistency: defined constraints and invariants remain satisfied;
• Isolation: concurrent transactions do not produce unacceptable interference;
• Durability: committed changes survive an appropriate system failure.

Consider transferring 100 currency units from account A to account B. The debit and credit must occur in the same transaction. If the debit commits but the credit fails, money has been lost from the application’s perspective.

BEGIN;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

If an error occurs, the transaction must be rolled back. A robust implementation also checks that both account identifiers exist and that the debit does not violate the overdraft policy.

Isolation levels represent trade-offs between concurrency and consistency. Common levels include Read Uncommitted, Read Committed, Repeatable Read and Serializable. Names and exact behavior vary by database engine. A higher isolation level can reduce anomalies but may increase locking, waiting or serialization failures. Applications must handle transient failures and retry only operations known to be safe to retry.

Transactions should normally be short. Holding a transaction open while waiting for user input, calling an external service or processing a large file increases lock contention and failure risk.

5. Validation, Constraints and Error Handling

Validation should occur at multiple boundaries:

1. User-interface validation provides immediate feedback.
2. Application validation applies domain rules and clear error messages.
3. Database constraints provide the final protection against invalid concurrent writes.

These layers are complementary. Checking that an email is unique in application code before insertion is useful for user experience, but it is not sufficient: two requests can pass the check simultaneously. A database UNIQUE constraint is required, and the application should translate a constraint violation into a suitable domain error.

Validation must distinguish missing, malformed and semantically invalid data. For example, a date can have a valid format but still be outside the permitted enrolment period. Sensitive values such as passwords should never be stored in plaintext; passwords require a slow, salted password-hashing algorithm managed through an established security library.

6. Schema Migrations

A migration is a versioned, repeatable change to a database schema. Migrations are preferable to manually edited production databases because they provide history, reviewability and reproducibility across development, testing and deployment environments.

A migration may:

• create or remove a table;
• add a nullable column;
• create an index;
• introduce a constraint;
• transform existing data;
• rename or replace a field through a staged process.

A safe expansion-and-contraction pattern is useful for a live system:

1. Add a new nullable column or table.
2. Deploy application code that can write both old and new representations.
3. Backfill existing records in controlled batches.
4. Verify completeness and correctness.
5. Switch reads to the new representation.
6. Enforce non-null or other constraints.
7. Remove the old representation in a later release.

Large data migrations should be observable, resumable and tested on production-like data. Backups are not a substitute for tested restoration procedures. Migration tools include Flyway and Liquibase in Java ecosystems and Alembic for SQLAlchemy projects.

7. Object–Relational Mapping

Object–relational mapping (ORM) maps program objects to relational rows. It reduces repetitive SQL for common operations, but it does not remove the need to understand SQL or database behavior. An ORM must resolve differences between object identity, inheritance, relationships and relational constraints.

JPA and Hibernate

JPA is a Java persistence specification; Hibernate is a widely used implementation. A simple entity may be written as follows:

@Entity
@Table(name = "learner")
public class Learner {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, length = 120)
    private String name;

    @Column(nullable = false, unique = true, length = 254)
    private String email;

    // constructors, accessors and domain methods omitted
}

A repository or EntityManager can persist and query the entity. JPA relationships should be designed deliberately. @ManyToMany is convenient for simple associations but often hides important attributes. When enrolment has a date or grade, model Enrolment as a separate entity rather than using a direct many-to-many mapping.

JPA entities commonly use lazy loading for collections. Accessing a lazy relationship outside an active persistence context can cause a lazy-initialization exception. Conversely, eagerly loading every relationship may retrieve excessive data.

SQLAlchemy

SQLAlchemy separates a SQL expression toolkit from its ORM. A declarative model can be written as:

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import String

class Base(DeclarativeBase):
    pass

class Learner(Base):
    __tablename__ = "learner"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(120), nullable=False)
    email: Mapped[str] = mapped_column(String(254), unique=True, nullable=False)

A session tracks objects, stages changes and coordinates transactions. A common service boundary is to open a session, perform a complete unit of work, commit on success and roll back on failure. Sessions should not be treated as global application objects; their lifetime should match a request or explicit unit of work.

ORM Performance and Correctness

The N+1 query problem occurs when one query retrieves N parent rows and the application then performs one additional query for each parent’s children. The result is N+1 database queries instead of an appropriate joined or batched strategy. Solutions include fetch joins, joinedload, selectinload, projections and explicit aggregate queries. The correct choice depends on result size and relationship cardinality.

Other ORM risks include accidental full-table updates, unbounded collection loading, inefficient cascades, stale entities and misunderstanding the persistence context. SQL logging, query plans, integration tests and performance measurements are professional necessities.

8. Relational and NoSQL Alternatives

Relational databases are strong when data has structured relationships, transactions, constraints and ad hoc querying requirements. NoSQL systems are a family of technologies rather than a single model:

A document database may suit a product catalogue whose attributes vary substantially by product type. A relational database is often preferable for orders, payments and inventory, where consistency and constraints are central. Polyglot persistence can be justified, but each additional database increases operational, security, backup and monitoring responsibilities. “NoSQL” should not be selected merely because the data is large or because a relational design was poorly constructed.

9. Case Study: Course Registration Service

Background

This illustrative case concerns a college registration service implemented by a small Java and Python development team. Learners register for modules, and administrators record grades.

Problem

The initial prototype stored enrolments in a single CSV file. It allowed duplicate registrations, had no reliable concurrent update behavior and made it difficult to answer questions such as “which learners are enrolled in a module?” A later requirement added grade publication and auditability.

Analysis

The domain contains Learner, Module and Enrolment. Enrolment has its own attributes, so it is an entity rather than merely a link. The design requires unique learner email addresses, unique module codes, valid grades and prevention of duplicate learner–module pairs. Registration and capacity checking must be transactional.

Approach and Solution

The team created three normalized tables with a composite key on Enrolment. The service validates input, while database constraints enforce final integrity. A registration transaction locks or otherwise safely checks the module capacity before inserting the enrolment. Flyway migrations manage schema changes in the Java deployment; Alembic manages an analytics database used by a Python service.

The Java service uses JPA for ordinary entity operations and a parameterized query for a registration report. The Python service uses SQLAlchemy with selectinload for a learner’s modules, avoiding one query per module. An audit table records who changed a grade, when, and the old and new values. Access control restricts grade changes to authorized staff, and personally identifiable information is minimized in logs.

Outcome

Duplicate registration attempts produce a controlled domain error rather than corrupting data. Concurrent registration behavior is defined and tested. Reports are faster because suitable indexes exist on module and learner foreign keys. Schema changes can be reproduced in test and production environments.

Learning Points

The central lesson is that persistence quality comes from the combination of domain modelling, database constraints, transaction design, migration discipline and measured ORM usage. No single framework can compensate for an unclear data model.

10. Governance, Security and Contemporary Practice

Persistent data may contain personally identifiable, financial, health or educational information. Teams should apply data minimization, purpose limitation, least-privilege access, encryption in transit and at rest where appropriate, secure secret management, retention schedules and controlled deletion. Privacy obligations depend on jurisdiction and sector; developers should consult applicable laws and institutional policy rather than assuming that technical access alone defines lawful use.

Audit trails should record significant actions without unnecessarily copying sensitive data. Backups must be encrypted, access-controlled and periodically restored in tests. Database credentials should not be committed to source control. Parameterized statements, dependency updates and protection against insecure deserialization are essential safeguards.

Modern systems increasingly use managed databases, containerized development environments, observability platforms and automated migration pipelines. AI-assisted coding can generate models or queries, but generated code requires human review for security, transaction semantics, privacy and vendor-specific SQL behavior. AI systems should not be supplied with confidential production data without approved controls.

Best-Practice Checklist

• Model business entities and relationships before choosing ORM annotations.
• Give every table a clear key and enforce relationships with foreign keys.
• Use constraints as well as application validation.
• Parameterize all externally influenced SQL values.
• Keep transactions short and define rollback behavior.
• Treat migrations as versioned, reviewed source code.
• Inspect generated SQL and monitor query performance.
• Control lazy loading and prevent N+1 queries.
• Select NoSQL because of a demonstrated access pattern or domain need.
• Protect sensitive data throughout its lifecycle.
• Test backups, migrations, concurrency and failure paths.

Chapter Summary

Data modelling translates domain concepts into entities, attributes and relationships. Relational schemas use keys and constraints to preserve integrity, while normalization reduces duplication and update anomalies. SQL defines and manipulates data, and parameterized queries protect against injection. Transactions provide atomic units of work, but isolation and concurrency require deliberate design.

Validation belongs in the user interface, application and database layers. Migrations provide controlled schema evolution, especially when expansion and contraction are needed for live systems. JPA/Hibernate and SQLAlchemy simplify common persistence operations, yet developers must understand sessions, persistence contexts, generated SQL, loading strategies and the N+1 problem. Relational and NoSQL databases address different requirements, so selection should follow domain structure, consistency needs and access patterns. Security, privacy, auditability, backups and human oversight remain part of professional persistence engineering.

Key Terms

Entity: A distinguishable domain object represented in persistent storage.

Attribute: A property describing an entity.

Primary key: A value or combination of values that uniquely identifies a row.

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

Cardinality: The permitted number of related instances in a relationship.

Normalization: Organizing data to reduce redundancy and anomalies.

Constraint: A database rule restricting valid states.

Index: An auxiliary structure that can accelerate data access.

Transaction: A logical unit of database work.

Atomicity: The all-or-nothing transaction property.

Isolation: The degree to which concurrent transactions are separated.

Persistence: Durable storage and retrieval of data.

ORM: Object–relational mapping between program objects and database structures.

JPA: A Java specification for persistence APIs.

Hibernate: A major implementation of JPA and ORM functionality.

SQLAlchemy: A Python SQL toolkit and ORM.

Migration: A versioned change to a database schema or data.

N+1 query problem: Excessive querying caused by one query per related object.

NoSQL: A group of non-relational database models.

Referential integrity: Consistency between foreign-key references and referenced rows.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Why is an associative table normally required for a many-to-many relationship?
3. State the four ACID transaction properties.
4. What problem does a database UNIQUE constraint solve?
5. What is the N+1 query problem?

Long Answer Questions

1. Explain how a conceptual domain model can be transformed into a normalized relational schema.
2. Discuss the role of application validation and database constraints, explaining why both are necessary.
3. Explain transaction isolation and the trade-off between concurrency and consistency.
4. Compare JPA/Hibernate and SQLAlchemy as ORM approaches, including their principal risks.
5. Compare relational, document, key–value and graph databases, giving an appropriate use case for each.

Analytical / Application Questions

1. Design tables and constraints for a library in which members borrow books, including loan dates and return status. Justify your keys and relationships.
2. A web application performs one query for 100 orders and then one query for each order’s customer. Identify the likely performance problem and propose two solutions.
3. A production system must replace a mandatory phone field with a new mobile_number field without downtime. Describe a safe migration sequence and the validation checks required.

Multiple Choice Questions with Answers

Question 1

Which structure is normally used to represent a many-to-many relationship between Learner and Module?

A. A nullable column in Learner containing all module titles

B. An associative table containing foreign keys to Learner and Module

C. A second primary key added to Module

D. A database index containing both entity names

Correct Answer: B. An associative table containing foreign keys to Learner and Module

Question 2

Which constraint prevents two rows from having the same email address?

A. CHECK

B. DEFAULT

C. UNIQUE

D. FOREIGN KEY

Correct Answer: C. UNIQUE

Question 3

What is the principal purpose of a transaction in a funds transfer?

A. To make SQL syntax portable

B. To ensure related changes succeed or fail as one unit

C. To remove the need for primary keys

D. To make every query execute faster

Correct Answer: B. To ensure related changes succeed or fail as one unit

Question 4

Which practice most directly prevents SQL injection?

A. Using longer table names

B. Adding an index to every column

C. Concatenating user input after escaping spaces

D. Binding user values as parameters

Correct Answer: D. Binding user values as parameters

Question 5

What does a migration primarily provide?

A. A versioned and reproducible change to a database schema

B. Automatic encryption of every database field

C. A replacement for all application validation

D. A method for avoiding transactions

Correct Answer: A. A versioned and reproducible change to a database schema

Question 6

A query loads 20 orders and then issues one customer query for each order. What problem does this illustrate?

A. Dirty write

B. N+1 query problem

C. Normalization to 3NF

D. Dead-letter processing

Correct Answer: B. N+1 query problem

Question 7

Which design best represents an enrolment that has a grade and an enrolment date?

A. A direct many-to-many relationship with no additional entity

B. An Enrolment entity or associative table with those attributes

C. A duplicated grade column in Learner only

D. A database index on Module.title

Correct Answer: B. An Enrolment entity or associative table with those attributes

Question 8

Which database type is generally strongest for enforcing complex relationships and multi-row transactions?

A. Relational database

B. Key–value store

C. Document cache only

D. Static file store

Correct Answer: A. Relational database

Question 9

Why can adding an index to every column be harmful?

A. Indexes prevent all SELECT queries

B. Indexes eliminate referential integrity

C. Indexes consume resources and can slow writes

D. Indexes make primary keys nullable

Correct Answer: C. Indexes consume resources and can slow writes

Question 10

Which migration sequence is safest for replacing a live field without immediate downtime?

A. Delete the old field, then design the replacement

B. Add the replacement, support both, backfill, switch reads, then remove the old field

C. Modify production manually and skip testing

D. Wait for users to recreate all records

Correct Answer: B. Add the replacement, support both, backfill, switch reads, then remove the old field