Designing a database

Rules in the schema

A schema can enforce the rules itself, so no program can break them by accident: NOT NULL (a value is required), UNIQUE, CHECK (any condition), and REFERENCES (a foreign key must point at a row that exists). SQLite checks foreign keys only after PRAGMA foreign_keys = ON, so set it on every connection.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE members (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);
CREATE TABLE trips (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    day  TEXT NOT NULL
);
CREATE TABLE signups (
    member_id INTEGER NOT NULL REFERENCES members(id),
    trip_id   INTEGER NOT NULL REFERENCES trips(id),
    role      TEXT NOT NULL CHECK (role IN ('hiker', 'leader', 'sweep')),
    PRIMARY KEY (member_id, trip_id)
);
""")
db.execute("INSERT INTO members (email, name) VALUES ('ana@ex.org', 'Ana Ruiz')")
db.execute("INSERT INTO trips (name, day) VALUES ('Fern Hollow loop', '2025-06-14')")
db.execute("INSERT INTO signups VALUES (1, 1, 'hiker')")

for bad in [
    "INSERT INTO members (email, name) VALUES ('ana@ex.org', 'Someone else')",   # email already used
    "INSERT INTO signups VALUES (1, 1, 'leader')",                               # same person, same trip
    "INSERT INTO signups VALUES (1, 99, 'hiker')",                               # no trip 99
    "INSERT INTO signups VALUES (1, 1, 'chef')",                                 # not a role
]:
    try:
        db.execute(bad)
    except sqlite3.IntegrityError as e:
        print("refused:", e)

The link table's primary key is the pair (member_id, trip_id), a composite key: one sign-up per person per trip. Every refusal above is a bug that would otherwise have reached the data quietly.

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE m (id INTEGER PRIMARY KEY, email TEXT UNIQUE)")
db.execute("INSERT INTO m (email) VALUES ('a@x.org')")
ok = 0
for e in ["b@x.org", "a@x.org", "A@x.org"]:
    try:
        db.execute("INSERT INTO m (email) VALUES (?)", (e,))
        ok += 1
    except sqlite3.IntegrityError:
        pass
print(ok)

Loading an export, twice-safe

The club will keep exporting the spreadsheet, so the loader has to turn flat rows into these tables, and running it again on the same file must change nothing. Two habits make that work:

1. Clean the logical key first: email.strip().lower(), so "Ana@ex.org " is the same member.
2. Get or create: insert a row only if its logical key is new (INSERT OR IGNORE, which skips a row that would break a UNIQUE rule), then look up its id. Whether the member was new or not, you end with their id.

A loader that gives the same result however many times it runs is idempotent. That's what lets a scheduled job be rerun after a failure without creating duplicates.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE members (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL);
""")

def member_id(db, email, name):
    email = email.strip().lower()
    db.execute("INSERT OR IGNORE INTO members (email, name) VALUES (?, ?)", (email, name.strip()))
    return db.execute("SELECT id FROM members WHERE email = ?", (email,)).fetchone()[0]

export = [("ana@ex.org", "Ana Ruiz"), ("bo@ex.org", "Bo Chen"), ("Ana@ex.org ", "Ana Ruíz")]
for _ in range(2):  # load the same export twice
    print([member_id(db, e, n) for e, n in export])
print(db.execute("SELECT * FROM members").fetchall())

Notice which name won: the first one loaded. Deciding that on purpose (first wins, last wins, or flag the difference for a person) is part of the design, and worth a comment in the code.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE trips (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, day TEXT NOT NULL)")


def trip_id(db, name, day):
    cur = db.execute("INSERT INTO trips (name, day) VALUES (?, ?)", (name, day))
    return cur.lastrowid


print(trip_id(db, "Fern Hollow loop", "2025-06-14"))

Getting the flat view back

Normalized tables don't lose anything: a join puts the spreadsheet view back together whenever you want it, now with every repeat guaranteed to agree. Questions about the links become simple, too:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE members (id INTEGER PRIMARY KEY, email TEXT UNIQUE, name TEXT);
CREATE TABLE trips (id INTEGER PRIMARY KEY, name TEXT UNIQUE, day TEXT);
CREATE TABLE signups (member_id INTEGER, trip_id INTEGER, role TEXT, PRIMARY KEY (member_id, trip_id));
INSERT INTO members VALUES (1, 'ana@ex.org', 'Ana Ruiz'), (2, 'bo@ex.org', 'Bo Chen'), (3, 'cy@ex.org', 'Cy Okafor');
INSERT INTO trips VALUES (1, 'Fern Hollow loop', '2025-06-14'), (2, 'Red Mesa climb', '2025-06-21'), (3, 'Pine Gap walk', '2025-07-01');
INSERT INTO signups VALUES (1, 1, 'hiker'), (2, 1, 'leader'), (1, 2, 'hiker'), (3, 2, 'leader'), (2, 2, 'sweep');
""")

for row in db.execute("""
    SELECT t.day, t.name, m.name, s.role
    FROM signups s JOIN members m ON m.id = s.member_id JOIN trips t ON t.id = s.trip_id
    ORDER BY t.day, s.role
"""):
    print(row)

print("on 2+ trips:", db.execute("SELECT m.name FROM members m JOIN signups s ON s.member_id = m.id GROUP BY m.id HAVING COUNT(*) >= 2").fetchall())
print("no leader yet:", db.execute("""
    SELECT t.name FROM trips t
    LEFT JOIN signups s ON s.trip_id = t.id AND s.role = 'leader'
    WHERE s.trip_id IS NULL""").fetchall())

The last query is the anti-join from the SQL analysis lesson. With the old spreadsheet, "a trip with no leader" for a trip nobody had signed up for couldn't even be asked.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE products (sku TEXT PRIMARY KEY, price REAL NOT NULL);
CREATE TABLE order_lines (order_id INTEGER, sku TEXT REFERENCES products(sku), qty INTEGER, unit_price REAL);
INSERT INTO products VALUES ('TE-101', 200);
INSERT INTO order_lines VALUES (1, 'TE-101', 1, 200);
""")
db.execute("UPDATE products SET price = 170 WHERE sku = 'TE-101'")   # a sale starts
db.execute("INSERT INTO order_lines VALUES (2, 'TE-101', 1, 170)")
print(db.execute("SELECT order_id, qty * unit_price AS paid FROM order_lines").fetchall())
print(db.execute("SELECT price FROM products").fetchone())