Model Online / On-Site
Duration
Modules 5 modules

Course Objective & Scope

No amount of index tuning or hardware scaling can rescue a poorly designed database schema from update anomalies, duplicate records and convoluted queries. Distinct from runtime query tuning (C0792), this course focuses on getting relational table design, keys, data types and integrity constraints right from the ground up.

It covers translating business rules into entity-relationship models, functional dependencies and 1NF, 2NF, 3NF and BCNF normalization, making disciplined denormalization trade-offs for read-heavy paths, choosing natural vs surrogate keys (IDENTITY, sequences, sequential UUIDv7), data type and constraint engineering, modelling hierarchies and historical/audit data, and zero-downtime expand-contract schema migrations.

Who Should Attend?

  • Backend and full-stack developers designing database schemas for new features and services
  • Data architects, DBAs and systems analysts responsible for data modelling and integrity standards
  • Engineering teams refactoring legacy schemas plagued by duplicated or inconsistent data
  • Prerequisite: basic SQL knowledge (CREATE TABLE, SELECT, JOINs)

Key Takeaways

  • Translate domain rules into clean conceptual, logical and physical relational schemas
  • Eliminate insertion, update and deletion anomalies by normalizing tables to 3NF and BCNF
  • Apply targeted denormalization, summary tables or materialized views where read performance demands it
  • Make sound choices on primary keys (bigint identity vs UUIDv4 vs UUIDv7), column data types and constraints
  • Evolve live production schemas safely using the expand-contract migration pattern and Flyway/Liquibase

Curriculum

Conceptual, Logical and Physical Data Modelling
  • Extracting entities, attributes and 1:1, 1:N and M:N associative relationships from domain requirements
  • Modelling inheritance/polymorphism: Single-Table, Class-Table and Concrete-Table strategies
  • Key selection: Natural vs Surrogate keys (Bigint Identity/Sequences, the random UUIDv4 page-split trap and sequential UUIDv7)
Normalization (1NF to BCNF) and Eliminating Data Anomalies
  • How insertion, update and deletion anomalies corrupt production data over time
  • 1NF (atomic values and removing repeating column groups) and 2NF (removing partial key dependencies)
  • 3NF (removing transitive dependencies) and Boyce-Codd Normal Form (BCNF) through hands-on exercises
Controlled Denormalization and Difficult Modelling Patterns
  • When to bend 3NF: balancing OLTP write integrity against read latency and Star/Snowflake reporting schemas
  • Historical point-in-time snapshots (e.g. order line price/address), precomputed totals and Materialized Views
  • Modelling trees/hierarchies (Adjacency List, Materialized Path, Closure Table) and avoiding the EAV anti-pattern
Physical Schema Optimization: Data Types, Constraints and Auditing
  • Right-sizing data types, exact fixed-point numbers (NUMERIC/DECIMAL vs FLOAT) and UTC timestamps
  • Enforcing invariants in the database with NOT NULL, FOREIGN KEY, CHECK and UNIQUE constraints
  • Soft-delete pitfalls (deleted_at with partial unique indexes) and Temporal / Bi-temporal audit table patterns
Zero-Downtime Schema Evolution and Database Refactoring
  • Recognizing locking DDL operations on large live tables (renaming columns, changing types, adding NOT NULL)
  • Applying the Expand-Contract (Parallel Change) pattern with Flyway or Liquibase
  • Workshop: refactoring a denormalized, anomaly-prone enterprise schema into a clean, optimized model

Other Courses in This Category

  • SQL Server Database Querying Fundamentals SQL Querying Fundamentals

    Foundational SQL Server course covering relational database concepts, data modeling, normalization, indexing, database objects (Tables, Views, Stored Procedures), and JOIN queries.

    Databases Corporate
    Live Course
    8 modules
    View
  • SQL Server Database Querying Training SQL Server Querying

    Comprehensive SQL Server and T-SQL querying course covering SSMS, SELECT workflows, multi-table JOINs (INNER, OUTER, CROSS, SELF), filtering and pagination, data types, and built-in Scalar, Aggregate, and Window functions.

    Databases Corporate
    Live Course
    7 modules
    View
  • Big Data Querying and Analytics with Apache Spark SQL

    Apache Spark SQL and the DataFrame API, Catalyst Optimizer and Adaptive Query Execution (AQE), Parquet/Delta Lake/Iceberg table formats, join strategies and fixing data skew — for teams building big-data analytics.

    Databases Corporate
    Live Course
    5 modules
    View
  • PostgreSQL for Developers: From Fundamentals to Advanced SQL

    PostgreSQL data types, advanced SQL (CTEs, window functions, LATERAL), JSONB document modelling, full-text search, B-Tree/GIN/GiST/BRIN indexes, PL/pgSQL, MVCC/locking and partitioning — for application developers.

    Databases Corporate
    Live Course
    5 modules
    View
  • PostgreSQL Database Administration, High Availability and Operations

    PostgreSQL architecture, memory and WAL tuning, MVCC/Autovacuum and bloat control, PgBouncer connection pooling, PITR backups with pgBackRest, Streaming/Logical Replication and Patroni high availability.

    Databases Corporate
    Live Course
    5 modules
    View
  • MySQL and MariaDB Development, Tuning and Administration

    InnoDB storage engine and clustered index architecture, query and index optimisation, transaction and lock analysis, backups, GTID replication and high availability — for developer and DBA teams running MySQL and MariaDB.

    Databases Corporate
    Live Course
    5 modules
    View

Get in touch