How Databases Work | From Data Models and Constraints to Queries, Transactions, Indexes and Reliable State

A database works when it can represent real entities and relationships accurately enough that many users and applications can create, retrieve and change data efficiently while constraints, transactions, concurrency control, durability and recovery keep the stored state internally trustworthy when operations overlap or systems fail.

A database is not simply a large spreadsheet or a folder full of files. It is a managed state system: a data model, identifiers, relationships, constraints, storage structures, query machinery, transaction rules, concurrency controls, indexes, logs, backups, replication and recovery mechanisms working together so applications can change shared state without silently destroying its integrity.

A database can preserve state correctly without proving that the state is true about the outside world. Database integrity and world truth are separate jobs.

Quick Read: The Whole Database Mechanism

REAL-WORLD ENTITY / EVENT → DATA MODEL → SCHEMA → RECORD / ROW / DOCUMENT → IDENTIFIER / KEY → RELATIONSHIP → CONSTRAINT → WRITE → TRANSACTION → VALIDATION → STORAGE → INDEX → QUERY → QUERY PLAN → READ → CONCURRENT ACCESS → ISOLATION / CONFLICT CONTROL → COMMIT / ROLLBACK → LOG / DURABILITY → BACKUP / REPLICATION → FAILURE → RECOVERY → MIGRATION → OBSERVED APPLICATION STATE → MODEL CORRECTION

The governing RFE is:

Can a database represent real entities and relationships accurately enough that many users and applications can create, retrieve and change data efficiently while constraints, transactions, concurrency, recovery and provenance keep the stored state trustworthy when operations overlap or systems fail?

1. Databases Represent a Model of the World

A school database may represent students, classes, teachers, enrolments and results. A bank database may represent accounts, balances and transfers. A library database may represent works, editions, holdings and borrowers.

The database does not contain the person, money, book or event itself. It contains structured records intended to represent them.

database record ≠ real-world object.

2. Database ≠ Spreadsheet

A spreadsheet is excellent for flexible human analysis. A database is designed for shared, persistent state that may be read and changed repeatedly by many users and applications.

Databases add mechanisms for identifiers, constraints, transactions, indexes, concurrent access, permissions, logging and recovery that ordinary spreadsheets do not provide in the same way.

3. Database ≠ Dataset

A dataset is a collection of data. A database is a managed system for storing and operating on data over time.

A CSV file can be a dataset without being a database. A database can contain many datasets, operational tables or document collections that continuously change.

4. The Data Model Chooses How Reality Is Represented

A relational model represents data through tables, rows, columns, keys and relationships. A document database can store nested field-value documents. Graph databases emphasise nodes and edges. Other systems specialise in key-value, time-series or analytical workloads.

MongoDB’s current documentation, for example, describes a document as its basic data unit, stored as BSON field-value pairs that can include arrays and nested documents. Relational systems such as PostgreSQL instead centre tables, rows, constraints and SQL.

data model ≠ storage format alone; it defines which structures and relationships the system treats as natural.

5. Schema Defines the Expected Structure

A schema describes tables or collections, fields, data types, relationships, constraints and other structural rules.

A schema can be enforced strongly by the database, enforced partly by applications, or remain flexible and evolve over time.

schema ≠ data; flexible schema ≠ no schema.

6. Tables Are Structures, Not Real Objects

A relational table groups records with a common structure. A row can represent a student, order, measurement or event—but only according to the chosen model.

If one human can have multiple enrolments, addresses or identities over time, forcing “one person = one flat row” may produce a poor model.

table ≠ real-world class; row ≠ person.

7. Primary Keys Stabilise Record Identity

A primary key or other unique identifier distinguishes one record from another.

Names are often poor identifiers because they can change or collide. Stable internal identifiers allow records to remain linked even when labels change.

primary key ≠ meaning; it stabilises identity inside the model.

8. Foreign Keys Represent Relationships

In relational systems, a foreign key can require a value in one table to refer to an existing record in another.

This protects referential integrity—for example, preventing an enrolment from referring to a student ID that does not exist.

But the foreign key does not capture every business rule. A student may exist yet still be ineligible for a particular course.

foreign key ≠ complete business rule.

9. Constraints Protect Invariants

Databases can enforce uniqueness, non-null requirements, valid ranges, relationships and custom conditions.

Constraints protect rules that must remain true no matter which application writes the data.

constraint ≠ application validation alone.

10. NULL Is Not Zero and Not an Empty String

In SQL, NULL represents missing or unknown information. It behaves differently from numeric zero, false or an empty string.

Collapsing these states can create subtle analytical errors. “No phone number recorded” is different from a phone number whose value is an empty string entered by mistake.

NULL ≠ zero ≠ empty string.

11. Writes Change Shared State

Insert, update and delete operations alter persistent state. Because later operations depend on that state, each write needs to respect identifiers, constraints and business rules.

A successful write means the database accepted the state transition under its rules. It does not prove the real-world assertion was correct.

12. Transactions Group Changes Into One Logical Unit

A transaction groups operations so they succeed or fail according to a defined consistency boundary.

A bank transfer, for example, should not permanently debit one account if the corresponding credit fails.

PostgreSQL’s current documentation exposes explicit transaction blocks through commands such as BEGIN, COMMIT and ROLLBACK, with configurable isolation levels.

transaction ≠ one SQL statement by definition; it is the intended logical unit of state change.

13. Atomicity Means All-or-Nothing at the Transaction Boundary

Atomicity prevents a multi-step transaction from leaving a partially committed result when the transaction fails.

This does not mean every surrounding business process is atomic. A payment, shipment and email notification may span several systems and require higher-level coordination.

14. Consistency Means Invariants Survive Valid Transactions

Database consistency means the state continues to satisfy declared constraints and valid transition rules after a transaction.

This is different from the distributed-systems use of “consistency”, where the term often concerns what multiple replicas can observe.

consistency has context; do not collapse every use of the word into one property.

15. Isolation Controls What Concurrent Transactions Can Observe

When many transactions run at once, each needs rules for what changes from other transactions it can observe and how conflicts are handled.

PostgreSQL’s current concurrency-control documentation describes this problem explicitly: multiple sessions may access the same data at the same time, and the system must maintain strict integrity while allowing efficient concurrent use.

16. MVCC Lets Readers See Consistent Versions

PostgreSQL uses Multiversion Concurrency Control (MVCC). Rather than forcing every reader and writer to wait on the same physical row state, the system can present transactions with appropriate snapshots of data while managing concurrent updates.

MVCC reduces unnecessary contention, but it does not remove every conflict. Transactions can still collide, deadlock or require retry depending on operations and isolation level.

17. Durability Carries Committed State Through Failure

Durability means that once a transaction is successfully committed according to the database’s guarantees, the system uses persistent storage and logging mechanisms so the result can survive subsequent process or machine failure within the stated failure model.

ACID ≠ “nothing can ever fail”. ACID defines transaction properties inside a failure model.

18. Rollback Restores the Transaction Boundary

When a transaction cannot complete safely, rollback abandons its uncommitted changes.

Rollback does not reverse actions that already escaped into external systems unless those systems participate in a broader coordination mechanism.

19. Queries Ask the Database to Produce a Result

A query describes which records, fields, relationships, filters, aggregations or orderings the application wants.

A query result is a derived view over the stored state at a particular logical time or snapshot.

query result ≠ complete database.

20. The Query Planner Chooses an Execution Route

Declarative query languages such as SQL allow the user to state the desired result without manually specifying every storage operation.

The database planner estimates possible execution strategies and selects a plan based on statistics, indexes, join methods and expected cost.

A logically identical query can become fast or slow depending on data distribution, indexes and plan quality.

21. Indexes Trade Extra Storage and Write Work for Faster Retrieval

An index is a derived structure that helps locate records without scanning every row or document.

Indexes consume storage and must be updated as data changes. Too few can make reads slow; too many can make writes more expensive.

index ≠ duplicate authoritative database; it is a maintained access path.

22. Database Index and Search Index Are Different

A database index usually accelerates access to database records under the database’s transactional rules. A full-text or vector search index may transform canonical records into structures optimised for relevance ranking, token search or similarity.

The search layer can be rebuilt from canonical state. It should not silently become the sole source of truth when its job is retrieval.

database ≠ search index.

23. Caches Solve a Different Performance Problem

A cache keeps copies of frequently needed data closer to the application or user so responses can be faster.

Cached data can become stale. The system therefore needs invalidation or expiration rules.

cache ≠ database; fast copy ≠ canonical state.

24. Concurrency Creates Race Conditions

Two users can read the same state and then both try to update it. If the database and application do not coordinate correctly, one update may overwrite another or both may claim a scarce resource.

Locks, optimistic version checks, serializable transactions, uniqueness constraints and other concurrency controls solve different classes of conflict.

concurrent execution ≠ parallel correctness.

25. Deadlocks Are Conflicts in Waiting Order

A deadlock can occur when transaction A waits for a resource held by transaction B while B waits for a resource held by A.

Database systems detect or prevent such cycles in different ways. Applications should be prepared for some transactions to abort and retry safely.

26. Replication Creates Additional Copies for Availability and Scale

Replication copies database state to additional servers or regions. It can improve read scale, availability and disaster tolerance.

Replica systems must define when changes become visible and what happens during network partitions or failover.

replication ≠ backup.

27. Replicas Can Faithfully Copy Mistakes

If an authorised process deletes the wrong rows or corrupts data logically, replication may propagate that mistake rapidly to every replica.

This is why high availability and historical recovery are different capabilities.

28. Backups Preserve Recoverable Historical State

A backup captures enough database state and associated logs to restore service after loss, corruption or operator error according to the backup strategy.

A backup that has never been restored successfully is only a hope.

backup ≠ tested recovery.

29. Recovery Objectives Define Acceptable Loss and Downtime

Operational systems often distinguish how much recent data loss can be tolerated from how long service can remain unavailable.

These requirements drive backup frequency, log retention, replication architecture and restoration testing.

30. Permissions Protect Who Can Read and Change State

Database users and application roles should receive only the privileges required for their jobs.

Authentication answers who the actor is; authorisation answers which reads, writes or administrative operations that actor may perform.

31. Audit Logs Preserve Important State-Change Receipts

For sensitive systems, it can matter not only what the current value is but who changed it, when, through which application and from which previous state.

Audit logging supports accountability, incident investigation and reconstruction, although audit logs themselves require protection and retention rules.

32. Schema Migration Changes the Model Without Losing the World

Applications evolve. Fields are added, relationships change, tables split, document shapes evolve and old values require conversion.

A migration should preserve identity and meaning while moving stored state into the new model.

The dangerous case is a technically successful migration that silently changes semantics—for example, merging “unknown” and “not applicable” into one value.

33. NoSQL Does Not Mean No Structure

Document and other non-relational databases can use flexible or application-shaped models, but they still require choices about identity, required fields, relationships, indexes, validation and lifecycle.

MongoDB’s current documentation, for example, describes documents as field-value structures and also supports multi-document ACID transactions for operations that require them.

NoSQL ≠ no schema ≠ no integrity requirements.

34. The Database Can Be Internally Correct and Externally Wrong

Suppose a user enters the wrong birth date but the date has valid syntax, the student ID exists and every transaction commits correctly.

The database has preserved the wrong assertion perfectly.

database integrity ≠ truth about the outside world.

35. Worked System 1: A Student Record Must Separate Person, Enrolment and Result

A learner can exist for years, join different classes and receive many assessment results.

A stronger model separates:

STUDENT → STUDENT_ID → ENROLMENT → CLASS → ASSESSMENT → RESULT → DATE / ATTEMPT.

Trying to store everything in one flat student row creates repeating columns, overwritten history or ambiguity about which result belongs to which class and attempt.

36. Worked System 2: A Bank Transfer Needs a Transaction Boundary

A transfer of $100 from Account A to Account B can require at least two balance-changing operations.

The safe logical route is:

BEGIN TRANSACTION → CHECK AUTHORISATION / FUNDS → DEBIT A → CREDIT B → RECORD TRANSFER → VALIDATE INVARIANTS → COMMIT.

If the credit fails, the debit should not remain committed as if the transfer succeeded.

37. Worked System 3: Two Users Try to Book the Last Seat

User A and User B both read “1 seat available”. Both then attempt to book.

Without a protected state transition, both can appear to succeed.

A correct design can use a transaction plus a uniqueness or capacity constraint, locking, optimistic version check or serializable isolation depending on the model.

The important invariant is not “both users saw the same number”. It is committed bookings must never exceed actual capacity.

38. Worked System 4: Canonical Record Corrected, Search Index Rebuilt

A knowledge object is stored in a canonical database with stable ID, title, owner, version and visibility. A derived search index contains an older title and ranks the wrong object.

The repair route is:

ERROR OBSERVED → CANONICAL DATABASE CHECK → RECORD CORRECTED → CHANGE LOGGED → INDEX REBUILT / UPDATED → CACHE INVALIDATED → RETRIEVAL TEST → CORRECT OBJECT RETURNED.

The search index is useful because it is fast and disposable. The canonical structured record remains the maintained source of operational identity.

39. Hostile Test: “The Database Accepted the Write, Therefore the Stored Fact Must Be True”

The conclusion is unsafe.

• Did the write satisfy database types and constraints?
• Was the actor authorised?
• Was the correct real-world entity identified?
• Did the application map the input to the correct field?
• Was the value observed, inferred or copied from another source?
• Could the source itself be stale or wrong?
• Did concurrent transactions preserve the required invariant?
• Was the write later corrected or superseded?
• Can provenance or an audit log show where the value came from?
• What external evidence would prove the stored value false?

the database can certify a valid state transition; only the world can finally certify whether the represented fact matches reality.

Where Database Explanations Commonly Break

Failure	What goes wrong	Repair question
Database-spreadsheet collapse	Shared transactional state is treated like an editable grid	Which constraints, transactions and concurrency controls are required?
Database-dataset collapse	A static collection is confused with an operational state system	Who writes, queries and updates it over time?
Database-search-index collapse	Derived retrieval structures become canonical truth	Which system owns the authoritative state?
Schema-data collapse	Structural rules become the records themselves	What does the schema define versus what data currently exists?
Row-reality collapse	One row is assumed to equal one complete real-world entity	Which histories and relationships are missing?
Primary-key meaning halo	Identifier becomes semantic truth	What real entity does the key refer to?
Foreign-key business-rule collapse	Referential integrity becomes full eligibility	Which domain rules remain outside the key relationship?
NULL-zero collapse	Missing information becomes a real zero value	Is the value unknown, absent, empty or actually zero?
Application-only validation	Another writer bypasses the business invariant	Which rules must be enforced by the database too?
Commit-backup collapse	Successful commit becomes historical recoverability	Where is the tested backup and restore path?
ACID-infallibility collapse	Transaction guarantees become “nothing can fail”	What failure model and external systems remain outside the transaction?
Concurrency-correctness collapse	Parallel requests appear individually valid but violate a shared invariant	What state transition must be serialised or constrained?
Index-authority collapse	Fast access path becomes source of truth	Can the index be rebuilt from canonical state?
Cache-database collapse	Stale copy becomes authoritative	What invalidates or refreshes it?
Replication-backup collapse	Copies are assumed to protect against logical deletion	Can yesterday’s valid state be restored?
Backup-recovery collapse	Stored backup files become proof of recoverability	When was restore last tested?
NoSQL-no-schema collapse	Flexible documents become unconstrained data	Where are identity, validation and relationship rules defined?
Integrity-truth collapse	Internally valid data becomes external fact	What observation or evidence confirms the stored assertion?

How to Read Any Database System

1. World object: What real entity, event or relationship is represented?
2. Model: Relational, document, graph, key-value or another structure?
3. Schema: What shape and rules are expected?
4. Identity: What stable key distinguishes records?
5. Relationships: How are related records connected?
6. Constraints: Which invariants must never be violated?
7. Write: Who or what may change state?
8. Transaction: What operations belong to one logical unit?
9. Isolation: What may concurrent operations observe?
10. Conflict: How are races, locks, deadlocks or retries handled?
11. Storage: Where does durable state live?
12. Index: Which access paths accelerate queries?
13. Query: How do applications retrieve or aggregate state?
14. Permissions: Who may read, write or administer?
15. Audit: Can important changes be reconstructed?
16. Replication: How are additional copies maintained?
17. Backup: Which historical states are recoverable?
18. Recovery: Has restoration been tested?
19. Migration: How does the schema evolve without losing meaning?
20. World return: What outside evidence would prove the stored state wrong?

Current Technical Anchors

• PostgreSQL 18 — Concurrency Control for current MVCC, transaction isolation, locking, deadlocks and concurrent consistency.
• PostgreSQL 18 — START TRANSACTION for explicit transaction blocks and isolation modes.
• PostgreSQL 18 — Indexes for database index structures and query acceleration.
• PostgreSQL 18 — Backup and Restore for logical, file-system and continuous-archiving recovery approaches.
• MongoDB 8.0 — Documents for the current field-value document model and nested BSON structures.
• MongoDB 8.0 — Transactions for multi-document transaction support where operations need atomic coordination.

Where This Fits in the eduKate Architecture

This article owns the persistent structured state → constraint → transaction → query → concurrency → recovery mechanism.

• How Metadata Works owns structured descriptions about resources and their exchange across systems.
• How Retrieval Works owns how stored knowledge is returned when needed.
• How Information Works owns representation and transmission generally.
• How Standards Work owns shared specifications and compatibility.
• How AI Works owns modelling, inference and generated outputs.
• How Libraries Work owns collection, cataloguing, discovery, access and preservation of knowledge resources.
• How Databases Work owns durable operational state, integrity constraints, transactions, queries, concurrency and recovery.

What This Article Does Not Claim

• It does not make a database identical to a spreadsheet, dataset, cache or search index.
• It does not make one data model universally superior.
• It does not make a schema identical to the data stored under it.
• It does not make keys or constraints complete models of business meaning.
• It does not make ACID a promise that hardware, software or surrounding systems can never fail.
• It does not make every replica observe every write at the same instant.
• It does not make replication equivalent to backup.
• It does not make having backups equivalent to tested recovery.
• It does not make NoSQL equivalent to “no schema”.
• It does not make database integrity equivalent to truth about the outside world.
• It does not make the fastest search representation the canonical state.

Observable Mastery Test

Choose one shared state system: student enrolment, seat booking, bank transfer, inventory, patient appointment, library loan or knowledge catalogue.

You understand how databases work if you can reconstruct:

real-world entity/event → data model → schema → key → relationship → constraint → write → transaction → storage → index → query → concurrent access → commit/rollback → durability → replication/backup → failure → recovery → model correction.

Then ask five correction questions:

• Which invariant must remain true when two users act at the same time?
• Which derived index or cache can be rebuilt from canonical state?
• Which failure would replication copy rather than protect against?
• When was the backup last restored successfully?
• What real-world observation could prove that a perfectly valid stored value is nevertheless wrong?

A database fails as trustworthy infrastructure when its model no longer matches the job, constraints can be bypassed, concurrent writes violate shared invariants, derived copies become authoritative by accident, or recovery exists only on paper.

A database is not understood when we can create a table or run a query. It is understood when we can explain how a real-world state becomes structured data, how many actors can change that state without breaking its invariants, how the system survives failure, and why an internally perfect database can still be wrong when the world says the recorded fact is false.