Database Design: From Idea to Schema

What is a database and why does design matter

database definition, structured vs unstructured data, DBMS role, design vs implementation, cost of bad design, real-world consequences

How to identify entities and attributes from requirements

entity definition, attribute definition, noun extraction technique, entity vs value, weak vs strong entity, attribute types

Primary keys and unique identifiers explained

primary key definition, natural key, surrogate key, composite key, NULL constraint, uniqueness guarantee, auto-increment vs UUID

Relationships between tables: one-to-many, many-to-many, one-to-one

relationship types, cardinality, one-to-many pattern, many-to-many junction table, one-to-one split, foreign key definition, referential integrity

What is an ER diagram and how to read one

ER diagram purpose, entity rectangles, attribute ellipses, relationship diamonds, crow's foot notation, reading cardinality, ER vs schema diagram

What is database normalization and why it matters

normalization definition, update anomaly, insert anomaly, delete anomaly, normal form levels overview, denormalization tradeoff

First Normal Form (1NF): atomic values and repeating groups

1NF definition, atomic value rule, repeating group problem, multi-valued attribute, row uniqueness, 1NF conversion steps

Second Normal Form (2NF): eliminating partial dependencies

2NF definition, partial dependency, composite primary key, functional dependency, 2NF violation detection, 2NF conversion example

Third Normal Form (3NF): eliminating transitive dependencies

3NF definition, transitive dependency, non-key to non-key dependency, 3NF violation detection, 3NF conversion, BCNF brief overview

Denormalization: when to break the rules deliberately

denormalization definition, read vs write tradeoff, reporting tables, materialized views, redundant columns for performance, when not to denormalize

Choosing the right SQL data types for every column

integer types, DECIMAL vs FLOAT, VARCHAR vs CHAR, TEXT vs VARCHAR, DATE vs DATETIME vs TIMESTAMP, BOOLEAN storage, ENUM pros and cons

NOT NULL, UNIQUE, DEFAULT, and CHECK constraints

NOT NULL purpose, UNIQUE constraint, DEFAULT values, CHECK constraint syntax, constraint naming, column vs table-level constraints

Foreign keys and referential integrity in SQL

foreign key syntax, referential integrity, ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE RESTRICT, ON UPDATE CASCADE, deferred constraints

Indexes: when to add them and when not to

index purpose, B-tree index, single vs composite index, index on FK columns, covering index, index overhead on writes, when not to index

NULL values: meaning, risks, and how to handle them

NULL semantics, NULL vs empty string, three-valued logic, NULL in comparisons, IS NULL vs = NULL, nullable FK columns, strategy for avoiding NULL

How to model hierarchical data in a relational database

adjacency list, self-referencing FK, recursive CTE, nested set model, closure table, choosing a hierarchy pattern, tree traversal query

Polymorphic associations: one table referencing multiple tables

polymorphic association definition, commentable pattern, type column approach, separate junction tables approach, pros and cons, FK constraint limitation

Soft deletes: how to keep deleted records in the database

soft delete pattern, deleted_at timestamp, is_deleted flag, filtered views, partial index on deleted_at, hard delete vs soft delete, audit implications

Audit tables: tracking who changed what and when

audit trail pattern, separate audit table, created_by, updated_by, changelog table structure, trigger-based audit, application-layer audit, GDPR considerations

Many-to-many with extra attributes on the junction table

enriched junction table, junction table attributes, order_items pattern, enrollment with grade, many-to-many payload columns, junction as first-class entity

Vertical vs horizontal scaling for databases

vertical scaling definition, horizontal scaling definition, read replicas, write bottleneck, scaling limits, schema design impact on scalability, connection pooling basics

Table partitioning: splitting large tables by range or list

partitioning definition, range partitioning, list partitioning, hash partitioning, partition pruning, partitioning by date, partition maintenance, partitioning limitations

Archiving old data: keeping production tables lean

data archiving strategy, archive table pattern, cutoff date, incremental archive job, hard delete after archive, cold storage, pg_partman, event sourcing reference

Sharding concepts: designing a schema that can shard

sharding definition, shard key selection, hotspot problem, cross-shard queries, shard-friendly schema, tenant ID pattern, hash vs range sharding

Optimizing schemas for read-heavy vs write-heavy workloads

read vs write optimization, OLTP vs OLAP schema, covering indexes, materialized views, column store vs row store, event sourcing overview, CQRS pattern introduction

Database migrations: versioning schema changes safely

migration definition, up and down migrations, migration file naming, Flyway, Liquibase, zero-downtime migration, idempotent migrations, rollback strategy

Zero-downtime schema changes: adding columns and tables safely

zero-downtime migration, expand and contract pattern, non-blocking ALTER TABLE, adding nullable columns, backfilling data, removing old columns, gh-ost tool, pt-online-schema-change

Database roles and permissions: least privilege for every user

database roles, GRANT and REVOKE, least privilege principle, read-only user, application user, admin user, row-level security, role-based access control

Schema documentation: writing a data dictionary

data dictionary definition, column-level comments, table-level comments, documentation tools, dbdocs.io, README schema docs, naming conventions documentation, onboarding value

Capstone: designing a complete schema from scratch

end-to-end design workflow, requirements analysis, ER diagram, normalization pass, constraints and types, indexing strategy, migration files, roles, documentation review