Software Development
Relational Database Design and SQL Tuning
An engineer-level course on the two decisions that determine database performance: how the schema is modelled and how the optimiser executes queries against it. Covers normalisation and deliberate denormalisation, keys and constraints, indexing strategy, execution plan reading, cardinality estimation, and query rewriting.
Overview
Practical learning for workplace transfer.
A query that returns in eight milliseconds on a laptop and forty seconds in production is almost never a hardware problem. It is a missing composite index, a predicate wrapped in a function so no index can be used, a statistics estimate off by three orders of magnitude, or an ORM issuing one query per result row. This course works from the engine outward: how rows are stored on pages, what a B-tree index can answer, how the planner estimates cardinality and chooses between nested loop, hash, and merge joins, and what a plan is telling you when estimated and actual rows diverge. Schema design is the upstream cause: participants learn when to relax a normalised model and what that costs.
Prerequisites
Practical SQL writing experience including joins, aggregation, and subqueries, plus familiarity with at least one relational engine.
Objectives
- Model a relational schema to third normal form and justify each deviation from it.
- Choose data types, keys, and constraints that make invalid states unrepresentable.
- Design composite, covering, partial, and expression indexes for real access patterns.
- Read an execution plan and locate the operator responsible for the cost.
- Diagnose cardinality misestimation, stale statistics, and non-sargable predicates.
- Rewrite slow queries and eliminate N+1 access patterns introduced by ORMs.
Target audience
- Backend developers whose applications are constrained by database latency
- Database administrators formalising indexing and maintenance standards
- Data engineers designing operational and staging schemas
- Solution architects reviewing data models before a build starts
- Application support engineers investigating production slowdowns
- Technical leads owning capacity, cost, and query performance budgets
Program outline
A clear structure for the learning journey.
Program outline
Outline points are grouped in one designed block instead of being treated as separate module cards.
Module 1: Relational Modelling and Normalisation
Entities, relationships, and cardinality captured before any table is created
First, second, third normal form, and BCNF applied to a real schema
Update, insert, and delete anomalies as the evidence a model is under-normalised
Deliberate denormalisation: what read speed costs in write complexity
Module 2: Physical Design, Types, and Constraints
Row storage, page layout, fill factor, and why column order can matter
Numeric, text, temporal, and JSON types and the cost of over-wide columns
Surrogate versus natural keys, unique and check constraints, and foreign key enforcement
Partitioning strategies for large tables and their effect on query pruning
Module 3: Indexing Strategy
B-tree structure, selectivity, and why leading column order decides usability
Covering indexes, included columns, and eliminating the table lookup
Partial, expression, hash, GIN, and full-text indexes for specific predicates
The write penalty: measuring index maintenance cost and removing unused indexes
Module 4: Reading Execution Plans
EXPLAIN and EXPLAIN ANALYZE: estimated versus actual rows and timing
Sequential scan, index scan, index-only scan, and bitmap scan compared
Nested loop, hash join, and merge join: why the planner picks each one
Statistics, histograms, ANALYZE frequency, and correlated-column misestimation
Module 5: Query Rewriting and Optimisation
Sargable predicates: keeping functions and casts off the indexed column
EXISTS, IN, and JOIN rewrites and correlated subquery elimination
Window functions and CTEs: when they clarify and when they block optimisation
Finding and fixing N+1 access patterns generated by ORM lazy loading
Module 6: Concurrency, Maintenance, and Sustained Performance
Isolation levels, locking behaviour, and diagnosing deadlocks from logs
MVCC, table bloat, vacuum and rebuild strategy on high-churn tables
Slow query logs, wait-event sampling, and building a repeatable tuning workflow
Schema migrations on large tables without extended locks or downtime
Materials provided
- Course workbook, annotated code samples, and reference notes
- Hands-on lab environment and starter repositories
- Exercises, checklists, and reusable code templates
- 4D Certificate of Completion
- Post-course technical guidance
Training Options
Programs can be delivered in-house, online, or in a blended format depending on your team's schedule, location, and learning objectives. When an external certificate or exam is included, certification rules and fees remain under the relevant awarding body's policies, while 4D provides the training and preparation support.
Why choose 4D
4D brings your slow query log into the room. Trainers take the ten worst statements from your own production workload, read their plans with the developers who wrote them and the DBA who supports them, and rebuild the indexes and schema decisions behind them. Teams leave with measured before-and-after timings and a tuning method they can repeat on the next incident.
Related courses
Python Programming Fundamentals
A hands-on introduction to Python for engineers and analysts who need working code rather than tutorial fragments. Participants build fluency with data types, collections, functions, files, and error handling, then assemble a small program that reads real data and produces a usable report.
View courseIntermediate Python and Clean Code Practices
Designed for developers whose Python works but is difficult to change. The course covers dataclasses, generators, decorators, context managers, and type hints, then applies the refactoring moves that turn a nine-hundred-line module into small tested units guarded by black, ruff, and mypy.
View coursePython Automation and Scripting for Work Tasks
Aimed at teams losing hours every week to repetitive file, spreadsheet, and reporting work. Participants automate those tasks with Python: batch file handling, Excel and CSV processing, REST API calls, email alerts, and scheduled jobs that run unattended and report clearly when something fails.
View course