Lesson 1Implementing constraints: NOT NULL, UNIQUE, CHECK, FOREIGN KEY cascades and ON DELETE/UPDATE strategiesImplement NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints to enforce business rules. Design ON DELETE and ON UPDATE strategies, including CASCADE and RESTRICT, to protect data integrity while supporting real business workflows.
Using NOT NULL to enforce required dataDesigning UNIQUE and composite keysCHECK constraints for business rulesFOREIGN KEY options and tradeoffsChoosing ON DELETE and ON UPDATE actionsLesson 2Table definitions: users (application auth) — columns, hashing, salts, account status, PKsCreate a secure users table for application authentication. Define columns for usernames, emails, password hashes, salts, and account status. Address password policies, lockout flags, and relationships to audit and ownership fields.
User identifiers and login fieldsPassword hash and salt storageAccount status and lockout flagsRole, permission, and profile linksAudit columns for user activityLesson 3Table definitions: clients — columns, types, constraints, PK and important FKsDefine the clients table with clear primary keys, unique constraints, and foreign keys to related entities. Choose suitable data types for names, contacts, and addresses, and design columns that support search, segmentation, and auditing needs.
Client identifiers and primary key choiceName, contact, and address columnsUnique constraints on emails and codesForeign keys from orders and invoicesAuditing and metadata columns for clientsLesson 4Entity modeling and normalization: mapping requirements to tables and relationshipsModel clients, orders, order_items, invoices, and users as entities with clear relationships. Apply normalization rules to reduce redundancy, map business rules to tables, and decide when controlled denormalization is justified for performance.
Identifying entities and relationshipsApplying 1NF, 2NF, and 3NF rulesModeling one-to-many and many-to-manyHandling optional and mandatory relationsWhen and how to denormalize safelyLesson 5Index strategy: candidate indexes (single-column and composite) and rationalePlan an index strategy for clients, orders, order_items, invoices, and users. Select candidate single-column and composite indexes based on query patterns, and balance read performance with write overhead and storage considerations.
Identifying high-value query patternsSingle-column versus composite indexesCovering indexes for critical queriesIndexing foreign keys and status fieldsMonitoring and tuning index usageLesson 6Table definitions: invoices — columns, types, relationships to orders and clientsDesign the invoices table and its relationships to orders, clients, and payments. Define columns for totals, taxes, and statuses, and ensure referential integrity. Address partial payments, credit notes, and numbering schemes for compliance.
Invoice identifiers and numbering rulesLinking invoices to clients and ordersTotals, taxes, and currency columnsInvoice status and lifecycle trackingHandling credit notes and adjustmentsLesson 7Data types selection: numeric, decimal, UUID vs serial, timestamps and timezone handlingChoose appropriate data types for identifiers, amounts, and timestamps. Compare numeric and decimal for money, UUID versus serial for keys, and discuss timezone-aware timestamps. Address precision, storage, and portability considerations.
Numeric versus decimal for monetary valuesUUID versus serial primary keysCharacter and text columns for namesTimezone-aware timestamp best practicesHandling date-only and interval fieldsLesson 8Table definitions: orders and order_items — columns, types, composite keys, FK integrityDefine normalized orders and order_items tables, including primary and composite keys, foreign key integrity, and essential columns. Learn how to capture quantities, pricing, statuses, and ensure consistent relationships to clients and invoices.
Core columns for orders and order_itemsChoosing primary and composite keysForeign keys to clients, users, invoicesModeling quantities, prices, and discountsStatus fields and lifecycle trackingLesson 9Materialized views and derived tables for aggregated invoice/order reportingExplore materialized views and derived tables that summarize orders and invoices. Learn how to design aggregates for revenue, taxes, and customer activity, refresh strategies, and performance tradeoffs between real time and batch reporting.
Identifying key reporting aggregatesDesigning summary and rollup tablesMaterialized view refresh strategiesHandling late-arriving and corrected dataIndexing and partitioning reporting tablesLesson 10Designing for multitenancy and soft deletes (tenant_id, deleted_at approaches)Design schemas that support multiple tenants and soft deletes. Compare tenant_id strategies, row-level isolation, and schema-per-tenant tradeoffs. Implement deleted_at columns, filtering patterns, and constraints that preserve historical data.
Tenant_id column versus schema per tenantRow-level security and tenant isolationImplementing deleted_at soft delete fieldsQuery patterns to exclude soft-deleted rowsRestoring and auditing soft-deleted records
