Many-to-Many

The shop wants to mark its games with tags, such as “multiplayer”, “family” or “story”, so that customers can find what they like. A game can have several tags, and a tag belongs to many games. That's a many-to-many relation, which you met in the lesson about joins, and it doesn't fit into the shop's existing tables:

• A column tag_id in games can hold only one tag per game.
• A column tags with a list, such as 'multiplayer, family', can't be joined with anything, the database can't check it, and searching it with LIKE is clumsy and finds wrong matches.

The answer is a third table, a junction table, with one row for each pair: this game has this tag. It has two foreign keys, one to each table, and the pair is its primary key. As the key has two columns, it's written after the last column: PRIMARY KEY (game_id, tag_id). Each game can then appear in many rows, and so can each tag, but the same pair can't appear twice.

You know such tables already. order_items joins orders and games (an order has many games, a game is in many orders), and reviews joins games and customers. Both have a primary key of two columns, and both show that a junction table can hold more about the pair: the quantity and the price, or the stars and the comment.

Here's the same for the platforms the games run on: a table of platforms, a junction table and a query that joins all three:

CREATE TABLE platforms (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
) STRICT;

CREATE TABLE game_platforms (
    game_id INTEGER NOT NULL REFERENCES games (id),
    platform_id INTEGER NOT NULL REFERENCES platforms (id),
    PRIMARY KEY (game_id, platform_id)
) STRICT;

INSERT INTO platforms (name)
VALUES ('PC'), ('Switch');

INSERT INTO game_platforms (game_id, platform_id)
VALUES (3, 1), (3, 2), (6, 1);

SELECT g.title, p.name AS platform
FROM game_platforms gp
JOIN games g ON g.id = gp.game_id
JOIN platforms p ON p.id = gp.platform_id
ORDER BY g.title, p.name;

Moon Farm runs on two platforms, so it has two rows in game_platforms. The query starts from the junction table and joins each of its foreign keys to its table, as you learned in the lesson about joins.

Typical mistake: writing PRIMARY KEY after both columns. That would make two primary keys, and a table can have only one:

CREATE TABLE game_platforms (
    game_id INTEGER PRIMARY KEY,
    platform_id INTEGER PRIMARY KEY
) STRICT;

Error on line 1: table "game_platforms" has more than one primary key