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.

This chapter covers what replication and sharding gain and break. It explains the difference between sync and async, why you can't read what you just wrote, skew in partition keys, when eventual consistency shows up, and reading CAP as "what to give up during a partition".

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 explains how indexes speed up lookups and the cost they impose on writes. It covers column order in composite indexes, patterns that stop indexes from being used, and how they differ from sharding and hashing.

This chapter covers reading and writing queries that join and aggregate across tables. It covers the evaluation order of SELECT, the difference between inner and outer joins captured as row-count changes, one-to-many increasing parent rows, GROUP BY granularity, the difference between COUNT(*) and COUNT(column), NULL three-valued logic, and triaging "the count doesn't add up".

This chapter explains shared and exclusive locks, the four transaction isolation levels and three read anomalies, the choice between optimistic and pessimistic locking, and the causes and avoidance of deadlocks. It covers how to hold a version column in the table and why raising the isolation level alone doesn't prevent contention without locks.

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.

This chapter covers the three positions where subqueries fit, correlated subqueries and EXISTS, writing in reading order with CTEs, and how window functions aggregate without folding rows. It covers the differences between ROW_NUMBER, RANK, and DENSE_RANK, running totals, top-N per group, and support versions per database.

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.

This chapter covers the three basic operations of a transaction (begin, commit, rollback) and the four ACID properties. It explains that rollback affects only your own transaction, that commits can't be undone, and when syntax errors are detected.