GuideIntermediate

Data Modeling And Database Design

Learn to design the schema of a relational database: from a real-world problem to a model, covering entities, attributes, relationships, keys, normalization, integrity constraints, types, and indexes. You work by modeling Reservo, a coworking room-booking system, from the entity-relationship diagram all the way to the full DDL. You come out knowing how to design a correct, normalized schema with integrity guaranteed by the database itself, and knowing when to denormalize with judgment.

64
lessons
8
modules
English · Spanish
available in
Included in the Club
access
Sign in to get started
NIEVA

Outcomes

What you'll be able to do

  • Understand why database design matters and walk the path from conceptual to logical to physical model
  • Identify entities, attributes, and relationships (1:1, 1:N, N:M), draw the entity-relationship diagram, and use a join table for N:M relationships
  • Design keys: primary, foreign, natural vs surrogate, composite, and enforce referential integrity with `PRAGMA foreign_keys`
  • Normalize a schema up to 3NF (1NF, 2NF, 3NF) and understand the insertion, update, and deletion anomalies it solves
  • Decide when to denormalize with judgment: the trade-off between read speed and the risk of inconsistency
  • Write constraints the database actually enforces: NOT NULL, UNIQUE, CHECK, DEFAULT, and FOREIGN KEY, without relying only on the application
  • Choose correct data types and design physical indexes, understanding their write cost and reading EXPLAIN QUERY PLAN
  • Design Reservo's complete schema from scratch: the DDL, a populated database that runs, and the justification for every design decision

Before you start

What you need to bring

It's for you if...

  • Backend devs who inherit or design schemas and want to stop improvising tables on the fly
  • Anyone preparing for technical interviews that ask you to normalize a schema or justify a key
  • Analysts or devs migrating from NoSQL or spreadsheets who need to understand the relational model in depth
  • Teams carrying data anomalies from a poorly normalized schema or one with missing constraints

Requirements and materials

  • Basic SQL: CREATE TABLE, INSERT, SELECT
  • Python 3 installed (the `sqlite3` module ships with the standard library)
  • No prior experience with normalization or entity-relationship diagrams required

Content

The syllabus, module by module

Open any of them to see its lessons.

  • Module 1 Introduction: From Problem to Model
  • What a relational database is and why tables
  • The cost of a bad schema
  • The three levels: conceptual, logical, and physical
  • From domain to entities
  • Your first table with `CREATE TABLE`
  • Basic `INSERT` and `SELECT`
  • Mini-project: model Reservo conceptually and create `rooms`

  • Module 2 Introduction: entities, attributes, and relationships
  • Entity vs attribute
  • Choosing attributes
  • The 1:N relationship with foreign keys
  • The N:M relationship with a junction table
  • The 1:1 relationship
  • The entity-relationship (ER) diagram
  • Mini-project: model Reservo's entities and relationships

  • Module 3 — Keys: identity and integrity for your schema
  • The primary key: every row's identity
  • `INTEGER PRIMARY KEY`, `rowid`, and `AUTOINCREMENT`
  • The foreign key and `REFERENCES`: connecting tables without breaking them
  • Natural vs surrogate: is the key business data or an artificial id?
  • The composite key: when identity needs two columns
  • Referential integrity: `PRAGMA foreign_keys` and `ON DELETE`
  • Mini-project: keys and integrity for Reservo's schema

  • Module 4 — Normalization: one truth, one place
  • The anomalies of a flat schema
  • First normal form (1NF): one value per cell
  • Second normal form (2NF): no partial dependencies
  • Third normal form (3NF): no transitive dependencies
  • Normalizing Reservo step by step
  • Higher normal forms (BCNF) and how far to go
  • Mini-project: taking a `bookings_flat` to 3NF

  • Module 5 — Denormalization: When to Break the Rules
  • What Denormalization Is
  • The Read/Write Trade-off
  • When 3NF Gets in the Way
  • Denormalization Techniques
  • Keeping Duplicated Data Consistent
  • Normalize First, Denormalize With a Measured Reason
  • Mini-project: Denormalize a Reservo Report, Measure the Improvement, and Show the Risk

  • Module 6 — Constraints and Integrity: the Schema as a Contract
  • Why the Database Enforces Invariants
  • `NOT NULL` and the Danger of `NULL`
  • Simple and Composite `UNIQUE`
  • `CHECK`: Business Rules in the Schema
  • Default Values (`DEFAULT`)
  • Total Integrity: Combining Everything
  • Mini-project: Full Constraints for Reservo's Schema

  • Module 7 — Types, Indexes, and Physical Design: From the "What" to the "How"
  • SQLite's Type Affinity and `STRICT` Tables
  • Choosing Types Well: Money, Dates, and Booleans
  • What an Index Is and When to Use It
  • The Cost of an Index: Writes and Space
  • Reading the Plan with `EXPLAIN QUERY PLAN`
  • Composite Index, `UNIQUE` Index, and Closing Out Logical vs. Physical
  • Mini-project: Choose Reservo's Types and Add the Indexes Its Queries Need

  • Introduction to Module 8: Project — Design Reservo's Database
  • The Entities and Their ER Diagram
  • The Keys: Primary and Foreign
  • Normalizing to 3NF (and the Justified Denormalization)
  • The Integrity Constraints
  • Types and Indexes: Physical Design
  • Populating and Verifying the Database
  • Project: Design Reservo's Database

Where it fits

This guide is part of something bigger

It's studied inside these programs, with support and dates.

SQL Mini-BootcampFree

Common questions

What people usually ask

Start whenever you like

Sign in to get started
Reviews

What students say

These reviews are from enrolled students who completed at least 50% of the course. We moderate reviews only on content grounds (spam, offensive language, personal data), never for being critical or negative.

No approved reviews yet.

Be the first to share your experience!