Related Tables

Real data comes in tables that point at each other: vendors and their products, members and their bookings. You'll link tables with foreign keys, join them back together, count and group, page through long lists the way an API should, and make searches fast and emails unique with indexes. Then you'll build a farmers market's vendor directory.

Updated

Tables That Point at Each Other

  1. Related Tables and Foreign Keys (Journeyman): One vendor sells many products, so the products go in a table of their own and each product row holds its vendor's id in a foreign key column that REFERENCES the vendors table, a link SQLite checks only once PRAGMA foreign_keys = ON is sent on the connection.
  2. Joining Tables (Journeyman): A JOIN puts two related tables back together in one query, matching each product to its vendor with ON and naming columns from both tables through short aliases, and a LEFT JOIN keeps the vendors that have no products yet, with None where a product would be.

Answers for an API

  1. Counting, Grouping and Paging (Journeyman): COUNT, SUM and AVG let the database add up rows instead of sending every one to Python, GROUP BY gives one answer for each vendor, and LIMIT with OFFSET hands an API one page of a long list at a time, with COUNT(*) giving the total beside it.
  2. Indexes and Unique Values (Journeyman): An index lets the database find rows by a column without reading the whole table, the way a book's index finds a page, a UNIQUE index makes it refuse a second row with the same email, and the sqlite3.IntegrityError it raises becomes an exception of your own that the rest of the app can understand.

Exercise: Farmers Market Vendors

  1. Exercise: Farmers Market Vendors (Journeyman): Write the Saturday Farmers Market's data code in one file, with a vendors table and a products table linked by a foreign key, a join, grouped counts that include a vendor with no products, one page of vendors with the total, and a unique email that raises your own DuplicateEmail, and let pytest check each on a fresh database.