Capstone: Schema and Data Model

Up to now you have queried tables that were already built — employees, departments, and projects. But real capstone work often begins with a gap: the database does not yet hold the facts you need to answer the stakeholder's question. Perhaps you need to track project milestones, or employee performance reviews, or contractor rates. Somebody has to design the table that will hold those facts, and in a small team that somebody is often you.

Schema design is the art of deciding where each fact lives. A well-designed schema makes every query simple and every report possible. A poorly designed one forces you to fight your own data — parsing comma-separated lists inside a single column, or updating the same value in ten different places because it was duplicated rather than linked.

This lesson teaches the two fundamental decisions: when to extend an existing table with a new column, and when to create an entirely new table. Both choices are driven by the same question — is this fact about one thing, or about a relationship between two things?

erDiagram
  PROJECTS ||--o{ MILESTONES : "has"
  DEPARTMENTS ||--o{ EMPLOYEES : "employs"
  DEPARTMENTS ||--o{ PROJECTS : "owns"
  PROJECTS {
    int id
    text name
    int department_id
    text status
    int budget
  }
  MILESTONES {
    int id
    int project_id
    text label
    text deadline
  }

Extending the schema: a new milestones table links to the existing projects table.

The anatomy of a new table

Creating a table follows the same rhythm you have seen in every CREATE TABLE lesson: name the table, list the columns with their types, and mark the primary key. The new skill is choosing which columns to include and how they relate to what already exists.

A new table should model one kind of thing. milestones tracks project checkpoints; contractors tracks non-employee workers. Each row in the new table represents one instance of that thing, and the columns describe its properties. If a property belongs to the thing itself — a milestone's label, a contractor's day rate — it lives on the new table.

If a property links the new thing to an existing thing — a milestone belongs to a project — you add a foreign-key column like project_id. That column is not a property of the milestone; it is a pointer. It lets you join back to projects and answer questions like 'Which milestones are overdue for Apollo Platform?' without storing the project name redundantly on every milestone row.

Create a milestones table, seed two rows, and join them to projects.

CREATE TABLE milestones (
    id INTEGER PRIMARY KEY,
    project_id INTEGER,
    label TEXT,
    deadline TEXT
);

INSERT INTO milestones (project_id, label, deadline) VALUES
    (1, 'Design review', '2025-03-15'),
    (1, 'Beta launch', '2025-06-01');

SELECT p.name, m.label, m.deadline
FROM projects p
JOIN milestones m ON p.id = m.project_id;

Choosing types for new columns

The type you declare sets the affinity for every value that enters the column, and SQLite's permissiveness means a bad choice will not raise an error — it will simply corrupt your data silently. A day_rate declared TEXT will accept '3000' and 'abc' with equal politeness, and a year-end sum will fail because one row holds text.

Use INTEGER for counts, identifiers, and anything you will sum or compare. Use TEXT for names, descriptions, and dates stored as 'YYYY-MM-DD'. Use NOT NULL on any column that every row must have; a milestone without a project_id is an orphan, and a contractor without a name is unidentifiable.

One special case is the boolean flag. SQLite has no BOOLEAN type, so flags are stored as INTEGER with the convention 1 for true and 0 for false. Name the column clearly — is_active rather than active — so the next reader knows it is a flag and not a count.

flowchart TD
  A["One table with repeating data"] --> B["Update one row, miss another"]
  B --> C["Inconsistent data"]
  C --> D["Split into related tables"]
  D --> E["Update once, everyone sees it"]
  style C fill:#b45309,color:#fff
  style E fill:#0e7490,color:#fff

Normalisation keeps each fact in one place. Update it once, and every query sees the change.

Create a notes table with a NOT NULL constraint, add rows, and read them back.

CREATE TABLE project_notes (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    note TEXT,
    created_on TEXT
);

INSERT INTO project_notes (project_id, note, created_on) VALUES
    (1, 'Scope approved', '2025-01-10'),
    (2, 'Awaiting budget sign-off', '2025-01-12');

SELECT p.name, n.note, n.created_on
FROM projects p
JOIN project_notes n ON p.id = n.project_id;

Extending versus creating: the decision

Not every new fact needs a new table. If the fact describes an existing thing — a project now has a priority level, an employee now has a phone number — add a column to the existing table. The rule of thumb is simple: one column per simple property, one new table per new kind of thing.

Create a new table when the fact has its own identity and its own one-to-many relationships. A project has many milestones, so milestones deserve a table. A department has many employees, so employees already have a table with department_id. If you tried to store milestones as columns on projects — milestone_1, milestone_2, milestone_3 — you would hard-code a limit and waste space on projects with fewer milestones.

The many-to-many case is the most common reason to create a junction table. An employee can have many skills, and a skill can belong to many employees. Neither table can hold the other as a single foreign key, so you create a third table with two foreign keys — employee_id and skill_id — that links the pair.

flowchart LR
  Q["New fact to track"] --> E{"Fits existing table?"}
  E -->|yes| A["Add a column"]
  E -->|no| N["CREATE TABLE"]
  style A fill:#0e7490,color:#fff
  style N fill:#0e7490,color:#fff

Decision flow: does the new fact fit an existing table, or does it need its own?

A type mistake in action: TEXT affinity silently stores 'abc' where a number was expected.

CREATE TABLE demo_orders (
    id INTEGER PRIMARY KEY,
    amount TEXT
);

INSERT INTO demo_orders (amount) VALUES ('120'), ('abc'), ('95');

SELECT id, amount, typeof(amount) FROM demo_orders;

Testing the schema before you commit

A schema looks correct when you read it, but the real test is whether it can answer the questions you planned in the previous lesson. Create the table, insert a few test rows, and run the report queries against them. If a join fails because you named the foreign key project instead of project_id, you will catch it now — not in front of the stakeholder.

Run SELECT * FROM sqlite_master WHERE type = 'table' to list every table in the database and confirm yours was created with the name you intended. Then run a query that joins your new table to an existing one and returns at least one row. If the join returns nothing, check whether the foreign-key values in your test data actually match the primary keys in the parent table.

Exercise

Create a milestones table with four columns: id (INTEGER PRIMARY KEY), project_id (INTEGER), label (TEXT), and deadline (TEXT).

CREATE TABLE milestones (
    -- define the four columns here
);

CREATE TABLE milestones (
    id INTEGER PRIMARY KEY,
    project_id INTEGER,
    label TEXT,
    deadline TEXT
);

Exercise

You are adding a deadline column to track project due dates. Which type is most appropriate in SQLite?

Exercise

This CREATE TABLE has two problems: budget is declared TEXT (so you cannot sum it), and id is missing PRIMARY KEY. Rewrite the statement correctly.

CREATE TABLE initiatives (
    id INTEGER,
    name TEXT,
    budget TEXT
);

CREATE TABLE initiatives (
    id INTEGER PRIMARY KEY,
    name TEXT,
    budget INTEGER
);

Exercise

Design a contractors table for non-employee workers. It needs an id (INTEGER PRIMARY KEY), name (TEXT that cannot be empty), day_rate (INTEGER), and active (INTEGER representing 1 for yes, 0 for no). Then insert two rows: (1, 'Zara', 3000, 1) and (2, 'Yusuf', 2500, 0). Use explicit column lists.

CREATE TABLE contractors (
    -- define columns
);

-- insert two rows with explicit columns

CREATE TABLE contractors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    day_rate INTEGER,
    active INTEGER
);

INSERT INTO contractors (id, name, day_rate, active) VALUES (1, 'Zara', 3000, 1), (2, 'Yusuf', 2500, 0);

Exercise

You need to track which employees have which skills. An employee can have many skills, and a skill can belong to many employees. What is the correct schema design?

Recap

Add a column when the fact describes an existing thing; create a new table when the fact has its own identity and relationships.
Use INTEGER for counts and money, TEXT for names and dates, and NOT NULL on anything every row must have.
A junction table links two tables in a many-to-many relationship with two foreign keys.
Storing lists in a single TEXT column breaks queries; normalise into a separate table.
Test every schema change by joining the new table to an existing one and confirming the result.

Next you will transform the data itself — cleaning, reshaping, and enriching it with CASE and derived tables.

Capstone: Schema and Data Model

The anatomy of a new table

Choosing types for new columns

Extending versus creating: the decision

Testing the schema before you commit

Recap

Checkpoint quiz

Go deeper — technical resources