Introduction to Database Design — Taking Junior Engineers to the Level Where They Can Design for Real Work
This book is continuously improved. If you notice something or have a suggestion, feel free to leave a comment.
What You'll Learn From This Book
This book is for junior engineers who have the basics of programming down, but are new to databases (or only know the basics). It systematically walks through business-application database design, up to the level where you can build a design from requirements on your own.
By the time you finish this book, you'll be able to:
- Explain in your own words the basic concepts of a relational database (tables / columns / primary keys / foreign keys / transactions / ACID)
- Build an ER diagram from business requirements and create a table design normalized up through third normal form
- Apply the typical patterns for many-to-many, authentication/authorization, and OAuth on your own
- Evaluate the trade-offs in design-time decisions (surrogate key vs. natural key / logical vs. physical deletion / normalization vs. denormalization)
- Check in advance for the aspects that come up in a design review (performance / security / extensibility)
Who This Book Is For
- Junior engineers (6 months to a few years of programming experience / new to databases, or basics only)
- At the stage of just about to start designing a DB schema for an API backend or a business application
- Also for frontend developers moving into backend work, or people moving from no-code to hand-written code
The scope of "actually" in "actually being able to design":
- Personal projects (hobby apps, portfolios)
- The initial design of a new project at work
Out of Scope (Topics This Book Doesn't Go Deep On)
- Database design for large-scale distributed systems (sharding, splitting a database across microservices)
- Schema migration for an existing system (a full-fledged migration strategy)
- Serious NoSQL design (the decision of when to use it is covered in Chapter 5; serious design is a separate guide)
- Database operations (full-scale backup, replication, and monitoring operations)
These are intermediate-to-advanced topics — learn them separately once you have the foundation from this guide.
How This Book Is Organized
This book is organized into four parts: 19 chapters, plus a glossary and a design template collection.
Part 1: Fundamentals — The World of Databases
Covers what a database is, why data is stored in an organized way, and basic operations and integrity.
| Chapter | Title | What you'll learn |
|---|---|---|
| 01 | The Basic Concept of a Database | An organized collection of data / IDs and uniqueness / searchability |
| 02 | The Relationship Between a System and Its Database | The three-tier structure / the role of a DBMS |
| 03 | The Importance of Design | The qualitative reasons it's hard to fix later |
| 04 | CRUD and ACID | Basic SQL operations / transactions |
| 05 | Introduction to Relational Databases | Multiple related tables / choosing between this and NoSQL |
| 06 | DBMS and SQL | The major RDBMSs / the four categories of SQL |
| 07 | Basic Terminology and Naming Conventions | Tables / columns / naming standards |
| 08 | Master vs. Transaction Data | Two natures of data, and the resulting design differences |
Part 2: Design — Building a Good Database
Covers the core of the design process — keys, normalization, and ER diagrams.
| Chapter | Title | What you'll learn |
|---|---|---|
| 09 | The Flow of Design | The three stages of conceptual → logical → physical |
| 10 | Key Concepts | Primary key / foreign key / surrogate key vs. natural key |
| 11 | Normalization | First through third normal form / when to denormalize |
| 12 | ER Diagrams | Visualization and mermaid notation |
| 13 | Design in Practice | Requirements → ER diagram → CREATE TABLE |
Part 3: Applied — Patterns You'll Often Encounter in Practice
Covers the typical patterns for many-to-many, authentication/authorization, and OAuth.
| Chapter | Title | What you'll learn |
|---|---|---|
| 14 | Many-to-Many | Junction tables / self-reference / RBAC |
| 15 | Authentication and Authorization | Passwords / sessions / MFA / audit logs |
| 16 | OAuth / OIDC | Authorization Code + PKCE / JWT |
Part 4: Practice — Comprehensive Exercises and Beyond
Practices the full flow of requirements → design → implementation with comprehensive case studies, and touches on the entry point to performance design in the advanced chapter.
| Chapter | Title | What you'll learn |
|---|---|---|
| 17 | Real-World Case Studies | Comprehensive exercises in product delivery management and task management |
| 18 | Normalization Exercises | An exercise collection for Chapter 11 |
| 19 | Introduction to Index Design | How to place indexes that speed up search / an introduction to EXPLAIN |
Reference
| File | What it contains |
|---|---|
| Glossary | A quick-reference dictionary of technical terms |
| Design Template Collection | Checklists for naming, normalization, and review criteria |
The Database and Language Used in This Book
Unless noted otherwise, this book's code examples are written in the PostgreSQL dialect.
- Why PostgreSQL: it ranks first as the "most used" database in the Stack Overflow Developer Survey 2025 (used by 58.2% of professional developers). It's also commonly adopted for new projects
- Dialect differences from other RDBMSs (MySQL / SQL Server / Oracle) are shown in comparison tables, including at the end of Chapter 4
- Application-code examples use SQL plus TypeScript / Python pseudocode as needed
How to Read This Book
If You're Reading Cover to Cover
- If your fundamentals are shallow: read straight through starting from Chapter 1 (about 15-30 minutes per chapter)
- If you already know the fundamentals: it's fine to start from Chapter 5 (Introduction to Relational Databases)
- If you just want the design practice: focus on Chapters 9-13 (the design section)
If You're Looking Something Up as Needed
- "What was normalization again?" → Chapter 11 + the glossary's normalization entry
- "I need to implement OAuth" → Chapter 16
- "Not sure how to name a table" → the Design Template Collection's naming conventions
How to Approach the Exercises
Each chapter ends with 1-2 design exercises (producing a design from requirements).
- First, think it through on your own (about 10-15 minutes)
- Then expand the sample answer (collapsed in a
<details>block) - Deepen your understanding through the sample answer's "common mistakes" and "alternative approaches"
The design exercises are an important safeguard against "feeling like you understood it just by reading." Work through them hands-on.
Related Guides
Once you have the foundation of database design from this guide, the next step is the implementation phase.
- Laravel × DDD × Clean Architecture — a "domain-model-first" approach for when you adopt DDD at the implementation phase. Its methodology differs from this DB guide's "requirements → tables" approach, so be conscious of how the two are positioned relative to each other and use them accordingly
- Introduction to PayloadCMS — a Next.js-native headless CMS. Auto-generates a DB schema while keeping flexibility
Confirming What You've Learned
Once you've read through this guide and finished the exercises, you should naturally be able to:
- Design the DB schema for any memo app or to-do app on your own
- List 3-5 considerations when asked, in a design review, "what are the problems with this table design?"
- Read an existing system's ER diagram and understand the business relationships it represents
- Judge, at design time, "is this master data or transaction data?" and "should the primary key be a surrogate key or a natural key?"
- Carry a "typical pattern" like OAuth or RBAC from requirements all the way through to implementation
If you're ever unsure which chapter of this guide to go back to, the glossary and the design template collection are your map. We hope this guide serves as a solid starting point for your journey as a junior database designer.
References
- Mick, 達人に学ぶDB設計徹底指南書 ("A Master's Guide to Thorough Database Design," Japanese-language), Shoeisha, 2012 (ISBN: 978-4-7981-2470-4)
- The primary reference behind this guide. A standard, systematic treatment of the fundamentals and pitfalls of database design — logical design, normalization, index design, backup/recovery, performance tuning, and more. The thinking in this book underlies how Part 1 (Fundamentals) and Part 2 (Design) of this guide are put together.
- A second edition (ISBN: 978-4-7981-8662-7) was published in August 2024; refer to it if you'd like more up-to-date content.
Let's start with Chapter 1: The Basic Concept of a Database.