Topic 9: Database Structuring, Table Designing

📖 6 min read · 🎯 advanced · 🧭 Prerequisites: tags-list, using-libraries-in-php

Why this matters

Picture this: you're building a sign-up form for an event, and three days before launch you realise attendee names are in one spreadsheet, emails in another, and payments in a third — and none of them match. That panic is real, and it's entirely avoidable. The fix isn't more spreadsheets; it's a well-designed database. In this lesson we're going to slow down and think before we type a single query — how do we structure our tables, what columns belong where, and how do we set constraints so bad data simply can't get in.

What You'll Learn

• Distinguish between relational and NoSQL databases and choose the right one
• Translate real-world requirements into normalized table schemas
• Write CREATE TABLE statements with primary keys, foreign keys, and constraints
• Query across related tables using JOIN to answer meaningful questions about your data

The Analogy

Think of a relational database like a well-run hotel. The Guests ledger holds every guest's name and contact details — once, with no duplicates. The Rooms ledger lists every room's type, floor, and amenities. A separate Bookings ledger simply says "Guest #42 is in Room #207 from Tuesday to Friday." You never copy the guest's address into the room record, and you never embed the room specs inside the guest file. Each ledger stays lean, authoritative, and linked — so a single change to a guest's phone number propagates everywhere automatically. That separation of concerns, and those links between ledgers, is exactly what relational database design gives you.

Chapter 1: Understanding the Basics

What Is a Database?

A database is a collection of organized information that can be easily accessed, managed, and updated. Databases range from simple spreadsheets to complex systems handling vast amounts of data.

Types of Databases

1. Relational Databases — store data in tables with rows and columns. Examples: MySQL, PostgreSQL.
2. NoSQL Databases — handle unstructured or semi-structured data and are optimized for specific data models. Examples: MongoDB, Cassandra.

For the Innovators' Summit, the class chose a relational database because the data is well-structured, the relationships between participants, events, and registrations are clear, and SQL gives powerful querying capabilities out of the box.

Chapter 2: Defining the Requirements

Before writing a single line of SQL, the class mapped out exactly what data needed to live where:

1. Participants — name, email, contact number, preferred track, dietary preferences.
2. Events — event name, description, date, time, location.
3. Registrations — the link between a participant and the event(s) they signed up for, plus when they registered.

Getting requirements right before touching the schema is the single most important step in database design. A schema built on fuzzy requirements requires painful migrations later.

Chapter 3: Designing the Tables

The Participants Table

Stores one row per person attending the summit.

CREATE TABLE Participants (
    ParticipantID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    ContactNumber VARCHAR(15) NOT NULL,
    Track VARCHAR(50) NOT NULL,
    DietaryPreferences VARCHAR(255)
);

The Events Table

Stores one row per scheduled session at the summit.

CREATE TABLE Events (
    EventID INT AUTO_INCREMENT PRIMARY KEY,
    EventName VARCHAR(100) NOT NULL,
    Description TEXT,
    EventDate DATE NOT NULL,
    EventTime TIME NOT NULL,
    Location VARCHAR(100) NOT NULL
);

The Registrations Table

Acts as the junction table linking participants to events. This is the standard relational pattern for a many-to-many relationship: one participant can attend many events, and one event can have many participants.

CREATE TABLE Registrations (
    RegistrationID INT AUTO_INCREMENT PRIMARY KEY,
    ParticipantID INT,
    EventID INT,
    RegistrationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ParticipantID) REFERENCES Participants(ParticipantID),
    FOREIGN KEY (EventID) REFERENCES Events(EventID)
);

The two FOREIGN KEY declarations tell MySQL to reject any insertion that references a ParticipantID or EventID that doesn't exist in the parent tables — a free consistency check.

Chapter 4: Ensuring Data Integrity

the trainer drew four lines under the whiteboard diagram and labeled them the Four Pillars:

• Primary Keys — every table has one; every row is uniquely identifiable. No two participants share a ParticipantID, no two events share an EventID.
• Foreign Keys — Registrations.ParticipantID must match a real row in Participants; Registrations.EventID must match a real row in Events. The database enforces this automatically.
• NOT NULL Constraints — fields like Name, Email, EventDate, and Location cannot be left blank. The database rejects the insert rather than storing garbage.
• UNIQUE Constraints — Email in the Participants table is declared UNIQUE, so the same address can never be registered twice, even if the application layer forgets to check.

Together these four mechanisms mean the database itself is the last line of defence for data quality — not just the application code.

erDiagram
    Participants {
        int ParticipantID PK
        varchar Name
        varchar Email
        varchar ContactNumber
        varchar Track
        varchar DietaryPreferences
    }
    Events {
        int EventID PK
        varchar EventName
        text Description
        date EventDate
        time EventTime
        varchar Location
    }
    Registrations {
        int RegistrationID PK
        int ParticipantID FK
        int EventID FK
        timestamp RegistrationDate
    }
    Participants ||--o{ Registrations : "registers via"
    Events ||--o{ Registrations : "filled by"

Chapter 5: Querying the Data

With the schema in place, the class demonstrated the queries that would power the summit dashboard.

Retrieve all participants

SELECT * FROM Participants;

Find all events a specific participant is registered for

SELECT e.EventName, e.EventDate, e.EventTime, e.Location
FROM Events e
JOIN Registrations r ON e.EventID = r.EventID
WHERE r.ParticipantID = 1;

The JOIN connects the Events and Registrations tables on their shared EventID column, then WHERE filters down to a single participant.

List all participants for a specific event

SELECT p.Name, p.Email, p.ContactNumber, p.Track, p.DietaryPreferences
FROM Participants p
JOIN Registrations r ON p.ParticipantID = r.ParticipantID
WHERE r.EventID = 2;

The pattern is symmetrical: swap which table you're JOINing from and which ID you're filtering by, and you can answer the opposite question.

🧪 Try It Yourself

Task: Set up the full Innovators' Summit schema in a local MySQL instance and run all three queries.

1. Open a MySQL client (MySQL Workbench, TablePlus, or mysql CLI).
2. Create a test database and switch into it:

CREATE DATABASE innovators_summit;
USE innovators_summit;

3. Paste and run the three CREATE TABLE statements from Chapter 3 in order: Participants first, Events second, Registrations last (foreign keys require the parent tables to exist first).
4. Insert a couple of rows:

INSERT INTO Participants (Name, Email, ContactNumber, Track, DietaryPreferences)
VALUES ('Ada Lovelace', 'ada@example.dev', '555-0101', 'Backend', 'Vegetarian');

INSERT INTO Events (EventName, Description, EventDate, EventTime, Location)
VALUES ('API Design Deep Dive', 'REST, GraphQL, and beyond.', '2026-06-10', '10:00:00', 'Hall B');

INSERT INTO Registrations (ParticipantID, EventID)
VALUES (1, 1);

5. Run the "find all events for participant 1" query.

Success criterion: You should see one row returned with API Design Deep Dive, 2026-06-10, 10:00:00, and Hall B. If the foreign key on Registrations is working correctly, try inserting a Registrations row with a non-existent ParticipantID — MySQL should reject it with an error.

🔍 Checkpoint Quiz

Q1. Why did the class choose a relational database over a NoSQL database for the Innovators' Summit?

A) NoSQL databases don't support dates
B) The data is well-structured with clear relationships between entities
C) MySQL is always faster than MongoDB
D) NoSQL databases can't store text

Q2. Given this statement, what happens if you try to insert a Registrations row where ParticipantID = 999 and no participant with that ID exists?

FOREIGN KEY (ParticipantID) REFERENCES Participants(ParticipantID)

A) The row is inserted with NULL for ParticipantID
B) The database throws a foreign key constraint error and rejects the insert
C) The database creates a new participant with ID 999 automatically
D) The row is inserted and the constraint is checked later

Q3. The RegistrationDate column is defined as TIMESTAMP DEFAULT CURRENT_TIMESTAMP. What does this mean in practice?

A) You must manually provide a timestamp on every insert
B) The column stores the date the table was created
C) The database automatically records the current date and time when a new row is inserted
D) The column only accepts timestamps in UTC

Q4. How would you modify the "list all participants for a specific event" query to return only participants whose Track is 'Frontend'?

A1. B — The summit's data has well-defined entities (participants, events, registrations) and explicit relationships between them, exactly what relational databases and SQL are built for.

A2. B — A FOREIGN KEY constraint actively enforces referential integrity; the database engine rejects any insert or update that would create a dangling reference.

A3. C — DEFAULT CURRENT_TIMESTAMP instructs MySQL to populate that column with the server's current date-and-time automatically at insert time, so the application never needs to supply it.

A4. Add AND p.Track = 'Frontend' to the WHERE clause:

SELECT p.Name, p.Email, p.ContactNumber, p.Track, p.DietaryPreferences
FROM Participants p
JOIN Registrations r ON p.ParticipantID = r.ParticipantID
WHERE r.EventID = 2
  AND p.Track = 'Frontend';

🪞 Recap

• Relational databases (MySQL, PostgreSQL) store data in tables linked by keys; NoSQL databases (MongoDB, Cassandra) handle unstructured or flexible-schema data.
• Start every schema design by writing out your data requirements — entities and their relationships — before touching SQL.
• Primary keys uniquely identify every row; foreign keys enforce that references between tables point to rows that actually exist.
• NOT NULL and UNIQUE constraints push data-quality enforcement down into the database engine itself, not just application code.
• The junction table pattern (here: Registrations) is the standard way to model many-to-many relationships in a relational schema.

📚 Further Reading

• MySQL CREATE TABLE reference — the source of truth for every column type and constraint option in MySQL
• PostgreSQL Data Types — comprehensive reference if you move to PostgreSQL
• Database Normalization Explained — why splitting data into multiple tables (instead of one fat table) keeps your schema healthy long-term
• ⬅️ Previous: Using Libraries in PHP
• ➡️ Next: JavaScript Object