01.4 Designing a small relational database

01 | START WITH THE PROBLEM

The school wants a simple current-membership database. It must store students and clubs, record who belongs to each club and distinguish members from leaders.

It needs to allow a student to join multiple clubs and a club to contain multiple students. For this first version, it stores only current memberships, not annual history, waiting lists or attendance.

Scope matters: a small design can be good without representing every future feature. Write the boundaries before choosing tables.

02 | WRITE THE BUSINESS RULES

• Each student has an identifier, first name and whole-number year level from 7 to 13.
• Each club has an identifier, a required unique name and a positive whole-number capacity.
• A current membership links one existing student to one existing club.
• The same student–club pair can appear only once.
• A membership role is member or leader; omitted roles default to member.

Students and clubs can exist with no memberships. The design permits more than one leader per club; if that is wrong for your situation, the requirements must be refined.

03 | FIND THE ENTITIES AND RELATIONSHIPS

An entity is a kind of thing we need to represent. Students and clubs are clear entities. Their many-to-many relationship becomes the Memberships table.

The membership has an attribute of its own: role. Maya can lead Game Design while being a member of Robotics, so role belongs on the relationship.

Students → Memberships is one-to-many; Clubs → Memberships is one-to-many. Both IDs together form Memberships’ primary key.

04 | WRITE A SMALL DATA DICTIONARY

A data dictionary makes intended meanings and constraints visible before implementation. It also gives you a checklist when reviewing the SQL.

05 | REMOVE UNNECESSARY REPETITION

If every membership copied the student’s name and club capacity, correcting either fact would require multiple edits. Missing one could leave conflicting versions of the same fact.

Store student details once in Students and club details once in Clubs, then link by keys. The IDs repeat in Memberships intentionally: they represent the relationships.

This is a practical introduction to normalisation. Not all repeated values are design errors; repeated foreign keys are often essential.

06 | CREATE AND POPULATE THE DATABASE

Use the in-memory Python runner from 01.3. Replace everything inside its sql = """...""" string with this complete script. Keep the wrapper and its connection setup.

Run the complete script in a fresh practice database. Parent tables are defined and populated before memberships. All names are fictional.

PRAGMA foreign_keys = ON;

CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL CHECK (length(trim(first_name)) > 0),
    year_level INTEGER NOT NULL
        CHECK (typeof(year_level) = 'integer' AND year_level BETWEEN 7 AND 13)
);

CREATE TABLE Clubs (
    club_id INTEGER PRIMARY KEY,
    club_name TEXT NOT NULL UNIQUE
        CHECK (length(trim(club_name)) > 0),
    capacity INTEGER NOT NULL
        CHECK (typeof(capacity) = 'integer' AND capacity >= 1)
);

CREATE TABLE Memberships (
    student_id INTEGER NOT NULL,
    club_id INTEGER NOT NULL,
    role TEXT NOT NULL DEFAULT 'member'
        CHECK (role IN ('member', 'leader')),
    PRIMARY KEY (student_id, club_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (club_id) REFERENCES Clubs(club_id)
);

INSERT INTO Students (student_id, first_name, year_level)
VALUES (101, 'Maya', 10), (102, 'Leo', 11), (103, 'Maya', 10);

INSERT INTO Clubs (club_id, club_name, capacity)
VALUES (1, 'Robotics', 12), (2, 'Game Design', 16);

INSERT INTO Memberships (student_id, club_id, role)
VALUES (101, 1, 'member'), (101, 2, 'leader'), (102, 1, 'member');

07 | INSPECT ALL THREE TABLES

In the Python runner, replace the final rows = ... block and its printing loop with the following. Keep it inside the try block, after executescript, at the same indentation level as the original rows assignment.

for table_name in ("Students", "Clubs", "Memberships"):
        print(table_name)
        # These are fixed table names written by us, not user input.
        rows = connection.execute("SELECT * FROM " + table_name)
        for row in rows:
            print(row)

Expect three students, two clubs and three memberships. Row order is not guaranteed without ORDER BY; later lessons explain sorting. In this inspection, check the stored facts rather than a particular row order.

The table names here are fixed trusted constants. Do not build queries by concatenating untrusted input; the later safe-queries lesson introduces parameterised values and controlled identifiers.

08 | READ THE EXPECTED DATA

Maya 101 belongs to both clubs, and Leo belongs to Robotics. Maya 103 currently has no membership. This is allowed: a student does not need a club to exist in the database.

We can follow links manually now. In section 03 of the SQL course, JOIN queries will combine these facts into useful result tables without duplicating storage.

09 | TEST RULES, NOT ONLY HAPPY PATHS

Do not run all tests as one script: the first rejected statement stops execution. Start fresh for each test so an earlier successful membership does not alter the expected outcome of a later one.

10 | KNOW WHAT YOUR CONSTRAINTS DO NOT ENFORCE

The capacity check requires a positive number. It does not count memberships or prevent a club from exceeding that capacity. Enforcing a cross-row capacity rule needs further application logic or a carefully designed database mechanism.

The schema also does not guarantee that a first name is genuine, that each club has a leader or that each student joins at least one club. Some of those are not requirements at all in this version.

Separate “stored a field” from “enforced a business rule”. Use tests to demonstrate the rules you actually implemented.

11 | EXTEND THE DESIGN

Challenge: add an optional meeting room for each club and a required joining-date text value for each membership. Decide whether joining_date should default or must be supplied explicitly.

Stretch: plan annual membership history. Explain why the current pair-only key prevents the same student joining the same club in a second year, and propose a key and uniqueness rule that match the new requirement.

12 | REVIEW AND NEXT STEP

Explain why there are three tables, trace one membership through its keys and show one accepted and one rejected test. If you can justify the design as well as run it, you are ready to retrieve data with SELECT, FROM and WHERE.

Keep your practice script and record the limits of this first version. The in-memory database disappears when the connection closes; a later file-based project can persist data deliberately.

KEEP LEARNING

Previous guide 01 | Relational database foundations: challenges

Reference: SQLite table constraints · SQLite data types · SQLite foreign keys · Python SQLite interface