The Wishlist Table

Welcome to the final project! Pixel Shop wants a new feature: a wishlist. A customer who likes a game but doesn't want to buy it right now saves it to their wishlist, and the shop can let them know later, when the game comes out or goes on sale. In this lesson, you'll build the database part of the feature yourself: you'll design and create the table, fill it, and then answer the shop's questions about it and about its sales. You'll need a bit of everything from the course.

The whole project is one file that grows. Each exercise starts with the solution of the one before, and you add to it. Remember that every run starts with a fresh copy of the shop's database: the table you create isn't there on the next run unless your file creates it again. That's why the CREATE TABLE (and later the INSERT) stays at the top of the file and runs every time you click Run.

Every good table starts with a question: what does one row say? Here, it's “this customer wants this game, since this day”. So the table needs three columns: the customer's ID, the game's ID and the date the game was added. A customer can wish for many games, and many customers can wish for the same game. It's a many-to-many relation, just like order_items between orders and games, or reviews between games and customers.

Such a table doesn't need an id of its own. Its primary key is the pair of the two IDs, a composite primary key. Besides identifying each row, it makes sure that the same game can't be on the same customer's wishlist twice.

The rest is as in the shop's own tables: IDs are INTEGER, the date is TEXT in the form 'YYYY-MM-DD', every column must have a value (NOT NULL), the IDs point at existing rows (REFERENCES), and the table is STRICT, so that SQLite checks the types. As a reminder, here's a table built the same way: tags of games, where a game can have many tags and a tag many games:

CREATE TABLE game_tags (
    game_id INTEGER NOT NULL REFERENCES games (id),
    tag TEXT NOT NULL,
    PRIMARY KEY (game_id, tag)
) STRICT;

The composite primary key comes after the last column, on a line of its own, with its columns in parentheses. Typical mistake: writing PRIMARY KEY after each of the two columns. That makes two primary keys, and SQLite stops with table "wishlist" has more than one primary key.

Once the table exists, SELECT * FROM wishlist; shows it. It has no rows yet, so the result is just the line with the names of the columns. That's a quick way to check that you got the columns right.