Glossary — Introduction to Database Design
This glossary collects the key terms that appear throughout this guide, arranged in alphabetical order. For a fuller explanation of each term, follow the link to the relevant chapter.
ACID Properties
The four properties a transaction must satisfy: Atomicity / Consistency / Isolation / Durability. Covered in detail in Chapter 4.
Authorization Code Flow
One of OAuth 2.0's flows. The Client receives an "authorization code" from the authorization server and exchanges it for an access token. Pairing it with PKCE is the modern standard. Covered in detail in Chapter 16.
BCNF (Boyce-Codd Normal Form)
A normal form stricter than third normal form, requiring that the left-hand side of every functional dependency be a superkey. Most business applications are fine with 3NF. Covered in detail in Chapter 11.
bcrypt
A password-hashing algorithm. Deliberately slow by design, making it resistant to brute-force attacks. Recommended alongside Argon2. Covered in detail in Chapter 15.
BIGINT
An 8-byte integer type in PostgreSQL / MySQL. Handles a value range (about 9.2 quintillion) where a sequential primary key won't run out. Use BIGSERIAL if you're worried about SERIAL (a sequential INTEGER).
Candidate Key
An attribute (or combination of attributes) that can uniquely identify a record. A single table can have multiple candidate keys. Covered in detail in Chapter 10.
Cardinality
The multiplicity of a relationship (one-to-one / one-to-many / many-to-many). Represented by the symbols on a line in an ER diagram. Covered in detail in Chapter 12.
CASCADE
One of the actions for a foreign-key constraint's ON DELETE / ON UPDATE. Automatically propagates a change on the parent record to the child records. Covered in detail in Chapter 10.
CHECK Constraint
A DB constraint guaranteeing that a column's value satisfies a condition. Example: CHECK (age >= 0 AND age <= 150). Covered in detail in Chapter 4.
Column
A column of a table. Corresponds to an item of data. Also called a "field" or an "attribute." Covered in detail in Chapter 7.
Constraint
A rule the DB uses to reject invalid data. Includes NOT NULL / CHECK / UNIQUE / PRIMARY KEY / FOREIGN KEY / DEFAULT. Covered in detail in Chapter 4.
CRUD
The four basic database operations: Create (INSERT) / Read (SELECT) / Update (UPDATE) / Delete (DELETE). Covered in detail in Chapter 4.
DBMS
A Database Management System. Software responsible for SQL parsing, data storage, concurrent-access control, and more. PostgreSQL, MySQL, Oracle, and SQL Server are well-known examples. Covered in detail in Chapter 6.
DDL / DML / DCL / TCL
The four categories of SQL. DDL = data definition (CREATE/ALTER/DROP), DML = data manipulation (SELECT/INSERT/UPDATE/DELETE), DCL = permission management (GRANT/REVOKE), TCL = transaction control (BEGIN/COMMIT/ROLLBACK). Covered in detail in Chapter 6.
Denormalization
A design that deliberately relaxes normalization, for performance requirements and similar reasons. "Normalize first, then selectively denormalize based on measurement" is the modern best practice. Covered in detail in Chapter 11.
Entity-Relationship Diagram (ER)
A diagram that visualizes a database's structure through entities and relationships. Covered in detail in Chapter 12.
First Normal Form (1NF)
A state where every cell holds an atomic value, with no repeating groups. Covered in detail in Chapter 11.
Foreign Key (FK)
A column that references another table's primary key. Expresses a relationship between tables and guarantees referential integrity. Covered in detail in Chapter 10.
Functional Dependency
Written as "A → B" — the relationship "once A is known, B is uniquely determined too." A foundational concept for understanding normalization. Covered in detail in Chapter 11.
Index
A data structure for speeding up searches. Just like an index at the back of a book, it lets you quickly find a record's location from a particular value. First introduced in Chapter 6; design decisions like which columns to index and composite-index ordering are covered in Chapter 19.
JWT (JSON Web Token, RFC 7519)
A signed token format made of three parts (header.payload.signature). Used as OpenID Connect's ID Token. Covered in detail in Chapter 16.
Many-to-Many
A relationship where either side can have multiple. Implemented with a junction table. Covered in detail in Chapter 14.
Master Data
Reference information (products, customers, departments, etc.). Changes infrequently and is small in volume. The counterpart concept to transaction data. Covered in detail in Chapter 8.
Natural Key
A business-meaningful attribute used directly as the primary key (e.g., an ISBN or an ISO country code). Only stable values should be adopted this way. Covered in detail in Chapter 10.
Normalization
A design technique for eliminating duplication and contradictions in data. First through third normal form is the standard for business applications. Covered in detail in Chapter 11 / Chapter 18 exercises.
NoSQL
A catch-all term for non-relational databases (MongoDB, Redis, Cassandra, etc.). How to choose between this and an RDB is covered in Chapter 5.
NULL
A special value representing "no value." Distinct from 0 or an empty string. Note that NULL = NULL evaluates to unknown, not false.
OpenID Connect (OIDC)
An authentication layer built on top of OAuth 2.0. Issues an ID Token (JWT). Covered in detail in Chapter 16.
Physical Delete / Logical Delete
Actually removing data with DELETE (physical) / keeping the row and just setting a delete flag (logical). Choose between them alongside GDPR / personal-data-protection law. Covered in detail in Chapter 13.
PKCE (Proof Key for Code Exchange)
A mechanism in OAuth 2.0 for preventing theft of the authorization code. RFC 9700 makes it mandatory for a public client (and recommended for a confidential client too), and it's a standard feature in OAuth 2.1. Covered in detail in Chapter 16.
Primary Key (PK)
The formal identifier that uniquely identifies each record in a table. NOT NULL + UNIQUE. Covered in detail in Chapter 10.
Query
A request made to a database. Generally, one SQL statement equals one query.
RBAC (Role-Based Access Control)
Role-based permission management. A three-layer structure of user → role → permission, implemented as many-to-many × many-to-many. Covered in detail in Chapter 14.
Record
A row of a table. One unit of data. Also called a "row" or a "tuple." Covered in detail in Chapter 7.
Relational Database (RDB)
A database that manages data in the form of tables and can express relationships between tables. Covered in detail in Chapter 5.
Schema
A namespace that groups tables and indexes together. PostgreSQL has a public schema by default. In MySQL, "schema" and "database" are nearly synonymous. Covered in detail in Chapter 7.
Second Normal Form (2NF)
First normal form, plus no partial functional dependency. Covered in detail in Chapter 11.
SERIAL / BIGSERIAL
PostgreSQL's auto-numbering types. SERIAL is a sequential INTEGER, BIGSERIAL is a sequential BIGINT. Equivalent to MySQL's AUTO_INCREMENT.
Snapshot
Storing a value as of a specific point in time. Example: an order line item's unit_price stores the product's unit price at order time, protecting it from future price changes in the product master. Covered in detail in Chapter 8 / Chapter 13.
Supertype / Subtype
Entities with an inheritance relationship. A structure of a parent holding common attributes (the supertype) plus children holding their own specific attributes (subtypes). Example: locations plus factories / stores. Covered in detail in Chapter 12 / Chapter 17.
Surrogate Key
A meaningless identifier the system generates automatically (a sequential number or a UUID). The modern recommendation is a hybrid: "a surrogate key as the primary key + a natural key as a UNIQUE constraint." Covered in detail in Chapter 10.
Table
The basic unit for storing data. A two-dimensional grid made up of rows (records) and columns. Covered in detail in Chapter 7.
Third Normal Form (3NF)
Second normal form, plus no transitive functional dependency. The standard business applications aim for. Covered in detail in Chapter 11.
TIMESTAMP / TIMESTAMP WITH TIME ZONE
Data types representing a date-time. Using the time-zone-aware form (TIMESTAMPTZ) is safer for international deployment.
Transaction
A mechanism that treats multiple operations as a single unit. Controlled with BEGIN / COMMIT / ROLLBACK. Guarantees the ACID properties. Covered in detail in Chapter 4.
Trigger
Logic that runs automatically in response to a DB event (INSERT / UPDATE / DELETE). Can be used for things like automatically updating an aggregate column, but carries a risk of lock contention. Covered in detail in Chapter 14.
UUID (Universally Unique Identifier)
A 128-bit random identifier. As a surrogate key, it has the benefit of being harder to guess than a sequential number. Generated with gen_random_uuid() (PostgreSQL).
If you're reading through this guide and feel "I'd like an explanation of this term," add it to this file. It becomes a form of collective knowledge supporting junior engineers' self-study.