All 18 chapters linked

Stage 01 · Data & SQL Foundations

Data Modeling and Database Design

Learn to turn ambiguous requirements into clear conceptual, logical, and physical database designs, then protect correctness with normalization, integrity rules, transaction boundaries, indexes, and workload-aware decisions.

Start first lesson View all chapters

18chapters

90lesson paths

Beginnerstarting level

Model + designlearning mode

Course state90 linked lesson paths

Course brief

Design databases from meaning first, then optimize for the real workload.

This course goes deeper than introductory SQL. It develops a full design workflow: domain discovery, entities and relationships, ER notation, relational mapping, keys and integrity, normalization, transaction-aware modeling, physical access paths, denormalization, temporal structures, schema evolution, governance, and a production-style capstone.

By the end

You will be able to

Turn business language into explicit data requirements and invariants
Build conceptual, logical, and physical data models
Normalize schemas and choose keys, constraints, and relationship mappings
Design transaction boundaries and evidence-based indexes
Plan denormalization, history, schema evolution, governance, and production reviews

Complete syllabus

18 chapters in prerequisite order.

Every chapter and lesson path is present from the beginning so navigation remains stable while lesson content is completed sequentially.

01

Chapter 1

From Requirements to Data Models

5 lessons

What Data Modeling Is and Why Database Design MattersAvailable lesson path

Turning Business Language into Data RequirementsAvailable lesson path

Facts, Rules, Scope, and AssumptionsAvailable lesson path

Conceptual, Logical, and Physical Data ModelsAvailable lesson path

A Repeatable Modeling Workflow and First Case StudyAvailable lesson path

02

Chapter 2

Entities, Attributes, and Identifiers

5 lessons

Discovering Entities and Entity TypesAvailable lesson path

Attributes, Domains, Optionality, and Derived ValuesAvailable lesson path

Candidate Keys and Business IdentifiersAvailable lesson path

Natural Keys Versus Surrogate KeysAvailable lesson path

Weak, Associative, and Reference EntitiesAvailable lesson path

03

Chapter 3

Relationships, Cardinality, and Participation

5 lessons

Relationship Semantics and Role NamesAvailable lesson path

One-to-One, One-to-Many, and Many-to-Many CardinalityAvailable lesson path

Mandatory and Optional ParticipationAvailable lesson path

Recursive, Ternary, and Higher-Order RelationshipsAvailable lesson path

Resolving Many-to-Many Relationships with Associative EntitiesAvailable lesson path

04

Chapter 4

ER Modeling Notations and Communication

5 lessons

Crow's Foot Notation from First PrinciplesAvailable lesson path

Chen Notation and Conceptual ModelingAvailable lesson path

Using UML Class Diagrams for Data ModelsAvailable lesson path

Naming Conventions and Diagram ReadabilityAvailable lesson path

Reviewing a Model with Domain Experts and DevelopersAvailable lesson path

05

Chapter 5

Mapping Conceptual Models to Relational Schemas

5 lessons

Mapping Entities and Attributes to Tables and ColumnsAvailable lesson path

Mapping One-to-Many and One-to-One RelationshipsAvailable lesson path

Mapping Many-to-Many RelationshipsAvailable lesson path

Mapping Supertypes, Subtypes, and InheritanceAvailable lesson path

Mapping Composite, Multivalued, and Derived AttributesAvailable lesson path

06

Chapter 6

Domains, Constraints, and Integrity

5 lessons

Domains, Data Types, and Meaningful Value SpacesAvailable lesson path

Entity Integrity, Keys, and UniquenessAvailable lesson path

Referential Integrity and Cascading ActionsAvailable lesson path

CHECK Constraints, Defaults, and Business RulesAvailable lesson path

Rules That Span Rows, Tables, and TimeAvailable lesson path

07

Chapter 7

Functional Dependencies and Data Anomalies

5 lessons

Functional Dependencies Explained VisuallyAvailable lesson path

Partial and Transitive DependenciesAvailable lesson path

Insert, Update, and Delete AnomaliesAvailable lesson path

Attribute Closure and Candidate-Key ReasoningAvailable lesson path

Lossless Decomposition and Dependency PreservationAvailable lesson path

08

Chapter 8

Normalization: First, Second, and Third Normal Forms

5 lessons

First Normal Form and Atomic DesignAvailable lesson path

Second Normal Form and Composite KeysAvailable lesson path

Third Normal Form and Transitive DependenciesAvailable lesson path

Normalizing a Real Requirements DocumentAvailable lesson path

When a Normalized Design Is Still a Bad DesignAvailable lesson path

09

Chapter 9

Advanced Normalization: BCNF, 4NF, and 5NF

5 lessons

Boyce-Codd Normal FormAvailable lesson path

Multivalued Dependencies and Fourth Normal FormAvailable lesson path

Join Dependencies and Fifth Normal FormAvailable lesson path

Normalization of Reference and Classification DataAvailable lesson path

Choosing a Practical Normal FormAvailable lesson path

10

Chapter 10

Transaction Boundaries and Consistency by Design

5 lessons

Modeling Business TransactionsAvailable lesson path

ACID Properties as Design ConstraintsAvailable lesson path

Invariants, Isolation, and Concurrent ChangesAvailable lesson path

Optimistic and Pessimistic Concurrency PatternsAvailable lesson path

Idempotency, Retries, and Safe State TransitionsAvailable lesson path

11

Chapter 11

Index-Aware Logical and Physical Design

5 lessons

How Indexes Change Physical DesignAvailable lesson path

Selectivity, Cardinality, and Index CandidatesAvailable lesson path

Composite Indexes and Column OrderAvailable lesson path

Covering, Partial, and Specialized IndexesAvailable lesson path

Write Cost, Storage Cost, and Index MaintenanceAvailable lesson path

12

Chapter 12

Workload Modeling and Access Patterns

5 lessons

Designing from Queries, Commands, and ReportsAvailable lesson path

OLTP Access Patterns and Hot PathsAvailable lesson path

Analytical Access Patterns and Large ScansAvailable lesson path

Read-Heavy, Write-Heavy, and Mixed WorkloadsAvailable lesson path

Measuring Before Changing the SchemaAvailable lesson path

13

Chapter 13

Denormalization, Caching, and Derived Data

5 lessons

Why and When to DenormalizeAvailable lesson path

Duplicated Attributes and Consistency OwnershipAvailable lesson path

Summary Tables, Materialized Views, and AggregatesAvailable lesson path

Caching Layers and Source-of-Truth DesignAvailable lesson path

Documenting and Testing Denormalized StructuresAvailable lesson path

14

Chapter 14

Time, History, Hierarchies, and Recursive Structures

5 lessons

Modeling Events, Effective Dates, and HistoryAvailable lesson path

Slowly Changing Business Data in Operational SystemsAvailable lesson path

Adjacency Lists and Recursive HierarchiesAvailable lesson path

Nested Sets, Materialized Paths, and Closure TablesAvailable lesson path

Modeling Networks and Graph-Like RelationshipsAvailable lesson path

15

Chapter 15

Semi-Structured and Polymorphic Data

5 lessons

When JSON Belongs in a Relational DatabaseAvailable lesson path

Schema-on-Write Versus Flexible AttributesAvailable lesson path

Polymorphic Associations and Their RisksAvailable lesson path

Entity-Attribute-Value Models: Uses and Failure ModesAvailable lesson path

Hybrid Relational and Document DesignsAvailable lesson path

16

Chapter 16

Schema Evolution, Migrations, and Compatibility

5 lessons

Designing Schemas That Can Change SafelyAvailable lesson path

Backward- and Forward-Compatible Database ChangesAvailable lesson path

Expand-and-Contract MigrationsAvailable lesson path

Backfills, Validation, and Zero-Downtime ChangesAvailable lesson path

Versioning, Rollback, and Database Changes in GitAvailable lesson path

17

Chapter 17

Security, Privacy, Governance, and Auditability

5 lessons

Least Privilege Starts with the Data ModelAvailable lesson path

Sensitive Data Classification and MinimizationAvailable lesson path

Tenant Isolation and Ownership BoundariesAvailable lesson path

Audit Trails, Provenance, and Immutable HistoryAvailable lesson path

Retention, Deletion, Lineage, and Data Quality RulesAvailable lesson path

18

Chapter 18

Capstone: Design a Production-Ready Database

5 lessons

Capstone Requirements and Domain DiscoveryAvailable lesson path

Conceptual and Logical Model ReviewAvailable lesson path

Normalize and Implement the Relational SchemaAvailable lesson path

Add Transactions, Indexes, Security, and Evolution PlansAvailable lesson path

Test, Document, Defend, and Present the DesignAvailable lesson path

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only

0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only assets compatible with the Ethereum/ERC-20 network. Do not send TRC-20/TRON assets.