Model a directory with tables and primary keys

Think of two classroom lists: one lists topics, and the other lists books about them. If every book row repeats a topic name, one spelling mistake can make a new-looking topic. Instead, store each topic once and let resources point to it.

A table stores one kind of fact. A row is one record; a column is one named property. A relational database connects related tables. A primary key is the value that identifies one row, even if its name changes. Choose a key that fits that job, rather than using it only to satisfy SQL.

1. Finish Programming Foundations with Python. The last lesson also uses the Node directory API, with a Python-only option if you have not taken Node.
2. Install Python 3.12 or newer. Check with python3 --version, or py -3 --version on Windows.
3. Use Python's built-in sqlite3 module. SQLite stores the database in one local file, so you need no database account.

Some lessons compare SQLite with PostgreSQL, a database that runs as a separate server. The course points out differences when they affect the example.

Save setup.py in a new sql-directory folder. Run it once with python3 setup.py. Delete only your practice directory.sqlite before repeating the initial setup, because these ordinary CREATE statements deliberately reject an existing schema.

import sqlite3
with sqlite3.connect('directory.sqlite') as db:
    db.execute('PRAGMA foreign_keys = ON')
    db.executescript('''
      CREATE TABLE topics (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL UNIQUE
      );
      CREATE TABLE resources (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        topic_id INTEGER NOT NULL REFERENCES topics(id)
      );
      INSERT INTO topics VALUES (1, 'math'), (2, 'reading');
      INSERT INTO resources VALUES
        (1, 'Algebra guide', 1), (2, 'Reading practice', 2);
    ''')
    print(db.execute('SELECT id, title FROM resources ORDER BY id').fetchall())

Expected output: [(1, 'Algebra guide'), (2, 'Reading practice')]. We supply explicit identifiers so the SQL's behavior is clear in both SQLite and PostgreSQL. SQLite's INTEGER PRIMARY KEY has special automatic rowid behavior; PostgreSQL needs an identity definition or another explicit default for automatically generated numbers. Later applications may choose UUIDs, but their benefits do not excuse ambiguous relationships.

A foreign key connects one table to a key in another. Here, resources.topic_id refers to topics.id. SQLite checks these links only when foreign-key enforcement is on. Enable it on every connection, before starting a transaction. One setup connection does not enable it for future connections. PostgreSQL checks declared foreign keys without this SQLite setting and does not use PRAGMA syntax.

Wrapping SQL in Python strings adds quotes and brackets to think about. A SQL console lets you type SQL directly. Python 3.12 and newer include one. From the sql-directory folder, run:

python3 -m sqlite3 directory.sqlite

At the sqlite> prompt, type SELECT id, title FROM resources ORDER BY id; and press Enter. You should see (1, 'Algebra guide') and (2, 'Reading practice'). Type .quit to leave. If you prefer a window with tables you can click through, the free DB Browser for SQLite (sqlitebrowser.org) opens the same file. Use whichever you like to try queries before putting them in code.

1. Draw the two tables before changing code. Label columns and keys, and draw the resource-to-topic link.
2. Add a third topic and three resources with explicit IDs. Identify which facts belong to topics and which to resources.
3. Compare the drawing with the actual rows. Investigate a duplicate-key error and explain why the new record needs its own identity.
4. Rename a topic without changing resource IDs. Check that the links survive. Do not overwrite a different record merely to avoid a duplicate-ID error.
5. In a schema notebook, explain each key and which properties may change. Allow time for both the drawing comparison and the error investigation.

Deliver a two-table directory with a reproducible offline setup and a relationship diagram. Include a note distinguishing SQLite's local database file from a hosted PostgreSQL service.

Next lesson: Turn data rules into constraints.

Sources: Python sqlite3, PostgreSQL constraints.