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 in Zimbabwe. 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 in Zimbabwean systems. 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 for Zimbabwe. 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 modelling and normalisation: mapping requirements to tables and relationshipsModel clients, orders, order_items, invoices, and users as entities with clear relationships in Zimbabwean setups. Apply normalisation rules to reduce redundancy, map business rules to tables, and decide when controlled denormalisation 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 in local databases. 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 for Zimbabwean enterprises. 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 in Zimbabwean time zones. 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 normalised orders and order_items tables, including primary and composite keys, foreign key integrity, and essential columns for local use. 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 9Materialised views and derived tables for aggregated invoice/order reportingExplore materialised views and derived tables that summarise orders and invoices for Zimbabwean reporting. 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 in Zimbabwean multi-user systems. 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
