This chapter frames a database as an organized collection of data and explains, for junior engineers, how that differs from managing data in files or spreadsheets, along with basic capabilities like uniqueness (IDs) and searchability.
This chapter organizes the differences between primary keys, foreign keys, candidate keys, composite keys, and surrogate vs. natural keys, and explains the modern best practice of a hybrid approach (surrogate key = PK + natural key = a unique constraint), for junior engineers.
This chapter builds up, step by step for junior engineers, the idea of normalization for eliminating data duplication and contradictions (update, insertion, and deletion anomalies) — from functional dependency, through first to third normal form, a note on BCNF, and selective denormalization for performance.
This chapter covers, for junior engineers, the basic notation of an ER diagram (Entity-Relationship Diagram) — entities, attributes, relationships, and cardinality — the steps for building an ER diagram from business requirements, and the major tools (mermaid / draw.io / PlantUML).
This chapter shows junior engineers, through two examples — a restaurant order slip and an internal SNS — the end-to-end process of extracting entities from a form or business requirements, building an ER diagram, normalizing it, and reaching CREATE TABLE.
This chapter covers implementing a many-to-many relationship with a junction table, using many-to-many with attributes (products and suppliers), self-referencing many-to-many (follows), a tag system, and RBAC (role-based access control) as examples, along with modern alternatives to trigger-based aggregation (application layer / materialized views / Redis).
This chapter covers, for junior engineers and from a database-design angle, the difference between authentication and authorization, password hashing (bcrypt/argon2), session management, multi-factor authentication (MFA / TOTP), account lockout (OWASP/NIST compliant), and audit logs.
This chapter covers, for junior engineers, OAuth 2.0's four roles, Authorization Code Flow + PKCE, OpenID Connect's ID Token (JWT), the modern standard following Implicit Flow's deprecation under RFC 9700, and DB design for social login.
Through two comprehensive case studies — a "product delivery management system" and a "task management system" — this chapter practices the full flow of requirements → ER diagram → table definitions → expected queries, integrating the concepts learned across earlier chapters as the capstone of this guide.
A collection of exercises to deepen the understanding of normalization covered in Chapter 11. Practice decomposing an unnormalized table up through third normal form, repeatedly, using common business cases (children's information, multiple categories, derived values, employee information).
This chapter covers, for junior engineers and focused narrowly on design-time decisions, how an index speeds up search (the intuition behind B-Trees), which columns to index (WHERE / JOIN / ORDER BY), composite-index column order, partial indexes, how to read EXPLAIN, the harm of over-indexing, and compatibility with UUID primary keys.
This chapter explains, for junior engineers, where a database sits within the three-tier structure of a web system (user / application / data) and the role of the DBMS that connects the application to the database.
This chapter explains why database design plays a role like a building's foundation, the qualitative reasons it's hard to fix later (you can't stop a live service, data-migration costs, the breadth of impact), and why data-design skills remain valuable even in the AI era.
This chapter explains the four basic database operations (CRUD), and the transactions and ACID properties (atomicity, consistency, isolation, durability) that handle multiple operations as a unit, using examples like bank transfers.
This chapter explains, for junior engineers, the basic concept of a relational database (RDB), the benefits and trade-offs of managing data as "multiple related tables" instead of "one giant table," and how to choose between RDB and NoSQL.
This chapter explains, for junior engineers, the role of a DBMS (SQL parsing, transaction management, permissions, optimization), the four categories of SQL (DDL/DML/DCL/TCL), and the characteristics and trade-offs of major RDBMS products (PostgreSQL / MySQL / SQLite / Oracle / SQL Server).
This chapter covers basic terms — table, column, record, primary key, foreign key, schema, index, view — and naming conventions that hold up as a global standard (plural table names, snake_case, Booleans prefixed with is_, and more), for junior engineers.
This chapter explains, for junior engineers, the idea of classifying database data into "master data" (dictionary-like reference information) and "transaction data" (day-to-day history), and the design and operational differences between them.
This chapter explains, for junior engineers, where database design fits within the overall flow of system development, and how the conceptual, logical, and physical design stages move from abstract to concrete.
A practical guide for junior engineers just starting to design DB schemas for an API backend or a business application. Across 19 chapters plus a glossary and a design template collection — Part 1 Fundamentals → Part 2 Design → Part 3 Applied → Part 4 Practice — you'll learn to go from requirements through ER diagrams and table definitions to SQL, by reading straight through.