.webp)
.svg)
Program overview
Online
On-site
Hybrid
Database Design and Data Modelling
Teach your teams to design transactional data models that survive contact with production. Three days covering requirements to conceptual, logical and physical models, entity relationship diagramming, keys, normalisation to third normal form, deliberate denormalisation, constraints, temporal history and schema evolution. Participants model a real business domain across the course and leave with a reviewed, defensible schema.
Duration:
3 Days
Level:
Intermediate
.avif)
1500+ users onboarded
Who will Benefit from this Training?
Training Objectives
Build a high-performing, job-ready tech team.
Personalise your team’s upskilling roadmap and design a befitting, hands-on training program with Uptut
Key training modules
Comprehensive, hands-on modules designed to take you from basics to advanced concepts
Download Curriculum
- From Business Requirements to a Data Model
- The three model levels: conceptual, logical and physical, and who each is for
- Eliciting entities, rules and constraints from stakeholder language
- Separating what the business means from how the application stores it
- Documenting assumptions and open questions before modelling begins
- Hands-on: extract a conceptual model from a written business brief
- Entities, Attributes and Relationships
- Identifying entities and distinguishing them from attributes
- Crow's foot notation and reading a diagram out loud as business rules
- Strong and weak entities, and identifying versus non-identifying relationships
- Naming standards that keep a large model readable
- Hands-on: draw and review a first-pass entity relationship diagram
- Keys and Identity
- Candidate, primary, alternate and foreign keys
- Natural versus surrogate keys and the arguments on each side
- Composite keys and where they cause downstream friction
- Sequences, identity columns, UUIDs and ULIDs and their storage and index behaviour
- Business keys that change, and designing for that eventuality
- Hands-on: choose and justify a key strategy for each entity in your model
- Normalisation to Third Normal Form
- Functional dependency as the underlying idea
- First normal form and atomicity in practice
- Second normal form and partial dependency
- Third normal form and transitive dependency
- The insert, update and delete anomalies each form eliminates
- Hands-on: normalise a flat spreadsheet extract into a working relational model
- Beyond Third Normal Form and Deliberate Denormalisation
- Boyce-Codd normal form and the cases that require it
- Fourth and fifth normal form at a practical level
- When denormalisation is the right decision and when it is laziness
- Redundancy with a maintenance plan: triggers, materialised views and derived columns
- Recording every denormalisation decision so the next team understands it
- Hands-on: denormalise one hot path and document the trade-off
- Cardinality, Optionality and Many-to-Many
- One-to-one relationships and whether they should be separate tables at all
- Resolving many-to-many with associative entities
- Attributes that belong on the relationship rather than either entity
- Optional relationships, nullable foreign keys and their query consequences
- Recursive and self-referencing relationships
- Hands-on: resolve every many-to-many in your model and add relationship attributes
- Data Integrity and Constraints
- Primary key, unique, check and not null constraints as design tools
- Foreign keys and referential actions: cascade, restrict, set null and their risks
- Enforcing rules in the database versus the application layer
- Domains, enumerations and reference tables for controlled vocabularies
- Deferred constraints and bulk load considerations
- Hands-on: implement the full constraint set and test it with deliberately bad data
- Modelling Time, History and Audit
- Current state versus full history, and choosing between them per entity
- Effective dating, valid time and transaction time
- System-versioned and temporal table support across platforms
- Soft deletes, their hidden costs and safer alternatives
- Audit trails, change logs and regulatory retention requirements
- Hands-on: add history tracking to two entities without breaking current-state queries
- Data Types and Physical Design
- Choosing numeric, text, date and boolean types deliberately
- Exact numeric types for money, and why floating point is not one of them
- Time zones, timestamps and storing instants correctly
- Row size, storage layout and the cost of oversized columns
- Initial index strategy and physical layout decisions
- Hands-on: convert the logical model into a physical schema with DDL
- Hierarchies, Polymorphism and Flexible Attributes
- Adjacency lists, path enumeration, nested sets and closure tables compared
- Modelling inheritance: single table, table per type and table per concrete class
- Entity attribute value patterns and why they usually cost more than they save
- JSON and semi-structured columns: appropriate uses and indexing options
- Deciding what genuinely needs to be flexible
- Hands-on: model a product catalogue with varying attributes three different ways
- Reviewing and Evolving the Model
- A structured design review checklist your team can reuse
- Common anti-patterns and how to spot them in an inherited schema
- Versioned migrations, backward compatible changes and expand-contract deployment
- Zero-downtime schema change patterns for large tables
- Documentation, data dictionaries and keeping the model current
- Hands-on: capstone review and defence of your model, plus a migration plan for one breaking change
Hands-on Experience with Tools
Training Delivery Format
Flexible, comprehensive training designed to fit your schedule and learning preferences
Opt-in Certifications
AWS, Scrum.org, DASA & more
.avif)
100% Live
on-site/online training
.avif)
Hands-on
Labs and capstone projects
.avif)
Lifetime Access
to training material and sessions
Skill-Gap Assessment
Analysing skill gap and assessing business requirements to craft a unique program
1
Personalisation
Customising curriculum and projects to prepare your team for challenges within your industry
2
Implementation
Supplementing training with consulting support to ensure implementation in real projects
3
Why this course
- Design faults are the expensive ones: A wrong key or missing constraint costs far more to unwind after two years of data than to fix at design time.
- A shared modelling language: Developers, analysts and architects leave using the same notation and the same review checklist.
- Built around one worked domain: Participants model a single business domain end to end rather than solving disconnected exercises.
- Covers evolution, not just design: Migration, versioning and history handling are treated as first-class topics because most work is on existing schemas.
Training objectives
- Translate business requirements into conceptual, logical and physical data models
- Produce clear entity relationship diagrams using standard notation
- Choose between natural, surrogate and composite keys with defensible reasoning
- Normalise a schema to third normal form and explain each anomaly removed
- Decide when denormalisation is justified and document the trade-off
- Resolve many-to-many relationships and model cardinality and optionality correctly
- Enforce integrity using constraints, referential actions and appropriate data types
- Model history, versioning and audit requirements without corrupting current-state queries
- Model hierarchies, flexible attributes and semi-structured data appropriately
- Review a data model against a checklist and plan a safe migration path
Who will benefit
- Application and backend developers
- Data engineers and analytics engineers
- Database administrators and solution architects
- Business analysts specifying data requirements
- Technical leads reviewing schema designs
.avif)
Lead the Digital Landscape with Cutting-Edge Tech and In-House " Techsperts "
Discover the power of digital transformation with train-to-deliver programs from Uptut's experts. Backed by 70,000+ professionals across the world's leading tech innovators.
.svg)
.svg)
.svg)
.svg)
.svg)
Recommended Training
Continue your learning journey with these hand-picked courses and bundles
Frequently Asked Questions
2. Will my team get any practical experience with this training?
.svg)
With our focus on experiential learning, we have made the training as hands-on as possible with assignments, quizzes and capstone projects, and a lab where trainees will learn by doing tasks live.
3. What is your mode of delivery - online or on-site?
We conduct both online and on-site training sessions. You can choose any according to the convenience of your team.
4. Will trainees get certified?
Yes, all trainees will get certificates issued by Uptut under the guidance of industry experts.
5. What do we do if we need further support after the training?
We have an incredible team of mentors that are available for consultations in case your team needs further assistance. Our experienced team of mentors is ready to guide your team and resolve their queries to utilize the training in the best possible way. Just book a consultation to get support.