32 docs tagged with "database"

A hub for the domain that collects notes on server-side development, including API design, authentication, and databases. It covers business logic design, data persistence, and building APIs.

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.

A collection of design templates and checklists that junior engineers can bring into real projects. It lists out naming conventions, normalization, key design, and review criteria, so you have something to check against right away on a new project.

Covers why to design domain-model-first, mapping patterns under the premise that the domain model and tables are not 1:1 (embedding / one aggregate across multiple tables / technical splitting), how the repository absorbs the gap, and index design including the leftmost-prefix rule for composite indexes and its trade-offs.

A quick-reference dictionary of the technical terms that appear throughout this guide. When you're reading a chapter and wondering "what was the definition of that term again?", check here for a quick answer.

This chapter organizes the operational tasks RDS handles as a managed service versus the design decisions Tasuku owns, covering instance class, storage, and Multi-AZ selection, master password integration with Secrets Manager, and completing the security group chain. It closes with a connectivity check using run-task and psql.

Explains transaction management using Laravel's DB::transaction(). Covers the ACID properties, when to place transactions inside the Repository (single aggregate) versus the UseCase (multiple aggregates), nested transactions (savepoints), and the implementation and use cases of optimistic and pessimistic locking.

インデックスが検索を速くする仕組みと、その代わりに払う更新時のコストを解説します。複合インデックスのカラム順序、インデックスが効かない書き方、シャーディングやハッシングとの用語の区別を扱います。

サブクエリを置ける 3 つの位置、相関サブクエリと EXISTS、CTE で読む順に書く方法、そしてウィンドウ関数が行を畳まずに集計する仕組みを扱います。ROW_NUMBER と RANK の違い、累計、グループごとの上位 N 件の出し方と、各データベースの対応バージョンを示します。

レプリケーションとシャーディングで何が得られ、何が崩れるかを扱います。同期と非同期の違い、書いた直後に読めない理由、分割キーの偏り、結果整合性が見える瞬間、そして CAP 定理を「分断が起きたとき何を諦めるか」として読む方法を示します。

トランザクションの基本操作 (開始・コミット・ロールバック) と ACID の 4 特性を解説します。ロールバックが取り消す範囲が自分のトランザクションに限られること、コミット後は取り消せないこと、構文エラーが発生するタイミングを扱います。

共有ロックと排他ロック、4 つのトランザクション分離レベルと 3 つの読み取り異常、楽観ロックと悲観ロックの使い分け、デッドロックの原因と回避方法を解説します。分離レベルを上げてもロック無しでは競合が防げない理由を扱います。

複数のテーブルを結合して集計するときの読み書きを扱います。SELECT の評価順序、内部結合と外部結合の違いを行の増減で捉える見方、1 対多で親の行が増えること、GROUP BY の粒度、COUNT(*) と COUNT(列) の違い、NULL の三値論理と「件数が合わない」ときの切り分けを扱います。