Data in Multiple Tables

Tags for Cards in Applications

---

The previous chapter introduced one-to-many application work with decks and cards. A sensible next question is how the same project can express a richer relationship: what if one card can have many tags, and one tag can belong to many cards?

That is a many-to-many relationship. In this chapter, the study tracker grows one step further by attaching the tags (built in the previous part) to cards from the previous chapter. In other words, the project keeps the earlier tag functionality and reuses it in a richer relational feature.

The Linking Table

The existing project already has cards and tags. To connect them, the schema needs one more table.

CREATE TABLE card_tags (
  card_id INTEGER NOT NULL REFERENCES cards(id),
  tag_id INTEGER NOT NULL REFERENCES tags(id),
  PRIMARY KEY (card_id, tag_id)
);

This table is different from cards and tags because it does not store a new main entity of its own. Instead, it stores the relationship rows. One row says that one chosen card is connected to one chosen tag.

For example, if the table contains the row (7, 3), that can be read in ordinary language as: card 7 has tag 3.

Once there are many such rows, one card may have many tags and one tag may appear on many cards.

The composite PRIMARY KEY (card_id, tag_id) is new here. It says that the pair of values together uniquely identifies one relationship row, which also prevents the same card-tag link from being stored twice.

In the project, add this table through a new migration file called 005_card_tags.sql. That migration should also insert a few seed relationships so the many-to-many structure is visible immediately.

Loading Exercise...

Showing One Card with Its Tags

Once the linking table exists, the next useful page is a card detail page. That page can show the card itself and the tags that belong to it.

One route can do that in three small steps. First load the chosen card, then load the tags already attached to it, and finally load the tags that are still available to attach.

@app.get("/cards/{card_id}")
def show_card(request: Request, card_id: int):
    with get_connection() as conn:
        card = conn.execute(t"""
            SELECT
              c.id,
              c.question,
              c.answer,
              d.id AS deck_id,
              d.name AS deck_name
            FROM cards AS c
            INNER JOIN decks AS d ON d.id = c.deck_id
            WHERE c.id = {card_id}
            """).fetchone()

        if card is None:
            return RedirectResponse("/decks", status_code=303)

        tags = conn.execute(t"""
            SELECT
              t.id,
              t.name
            FROM card_tags AS ct
            INNER JOIN tags AS t ON t.id = ct.tag_id
            WHERE ct.card_id = {card_id}
            ORDER BY t.name
            """).fetchall()

        available_tags = conn.execute(t"""
            SELECT
              t.id,
              t.name
            FROM tags AS t
            WHERE NOT EXISTS (
              SELECT 1
              FROM card_tags AS ct
              WHERE ct.card_id = {card_id}
                AND ct.tag_id = t.id
            )
            ORDER BY t.name
            """).fetchall()

    return render(
        request,
        "card_detail.html",
        card=card,
        tags=tags,
        available_tags=available_tags,
    )

This route is a good worked example because each query has a different job. The first query loads one card together with the deck it belongs to, using an inner join because every card belongs to exactly one deck. The second query follows the many-to-many path from card_tags to tags. The third query uses NOT EXISTS to find the tags that are not yet attached to the chosen card, so the form can offer only those.

As in the previous chapter, the card is loaded first, and the route returns early if no such card exists. The later queries then run only for a card that is really there.

After reading the route, it helps to ask what one row in card_tags represents. The answer is simple: one relationship between one chosen card and one chosen tag.

Loading Exercise...

Linking to the Card Page

If the application has a card detail page, the user needs a clear way to open it.

The most direct place for that link is the existing deck detail page from Chapter 9. Each listed card can include a View card link.

More concretely, place the link inside the existing {% for card in cards %} loop in app/templates/deck_detail.html, that is, in the part of the template that already renders one visible item for each card:

{% for card in cards %}
  <li>
    <strong>{{ card.question }}</strong>
    - {{ card.answer }}
    <a href="/cards/{{ card.id }}">View card</a>
  </li>
{% endfor %}

That keeps the relationship path visible in the application itself. The user starts from one deck, sees the cards that belong to it, and then moves to one chosen card.

Rendering the Card Detail Page

The matching template can show the card, its deck, its tags, and the add-tag form.

For example, app/templates/card_detail.html could look like this:

<!doctype html>
<html lang="en">
  <body>
    <h1>{{ card.question }}</h1>
    <p>{{ card.answer }}</p>
    <p>
      Deck:
      <a href="/decks/{{ card.deck_id }}">{{ card.deck_name }}</a>
    </p>

    <h2>Tags</h2>

    {% if tags %}
      <ul>
        {% for tag in tags %}
          <li>{{ tag.name }}</li>
        {% endfor %}
      </ul>
    {% else %}
      <p>No tags yet.</p>
    {% endif %}

    {% if available_tags %}
      <h2>Add tag</h2>
      <form method="post" action="/cards/{{ card.id }}/tags">
        <label for="tag_id">Tag</label>
        <select id="tag_id" name="tag_id">
          {% for tag in available_tags %}
            <option value="{{ tag.id }}">{{ tag.name }}</option>
          {% endfor %}
        </select>

        <button type="submit">Add tag</button>
      </form>
    {% endif %}
  </body>
</html>

The same separation of concerns applies here as in Chapter 9. The route is still responsible for choosing and loading the right rows. The template is only responsible for showing the values it receives in a readable page.

The page also makes the many-to-many relationship visible in ordinary language. A card page can list several tags, and those tags come from the linking table rather than from extra columns stored directly on the card row.

Loading Exercise...

The Route That Adds a Tag to a Card

The next natural question is how a user can attach an existing tag to one chosen card.

The matching route reads the chosen card from the path and the chosen tag from the form:

@app.post("/cards/{card_id}/tags")
def add_tag_to_card(card_id: int, tag_id: int = Form(...)):
    with get_connection() as conn:
        conn.execute(t"""
            INSERT INTO card_tags (card_id, tag_id)
            VALUES ({card_id}, {tag_id})
            ON CONFLICT DO NOTHING
            """)

    return RedirectResponse(f"/cards/{card_id}", status_code=303)

This route is worth reading carefully. The card_id comes from the URL path because the page is already about one chosen card. The tag_id comes from the form because the user selects one existing tag from the dropdown.

So the route does not create a new tag. Instead, it creates one new relationship row in card_tags. That is the key difference between creating an entity and creating a many-to-many connection between two existing entities.

The ON CONFLICT DO NOTHING clause makes the operation safe to retry: if the same card-tag link already exists, the composite primary key on card_tags would normally prevent the insert with an error, and this clause tells PostgreSQL to quietly skip the insert instead.

Loading Exercise...

What to Check

When this feature is complete, it helps to check a few things through the browser:

• does the deck detail page include a View card link for each listed card?
• does /cards/{card_id} show the chosen card and its current tags?
• does the page show No tags yet. when no tags are attached yet?
• does the add-tag form use the label Tag and the button text Add tag?
• after submitting the form, does the chosen tag appear on that same card page?

These checks verify both sides of the many-to-many feature. The read side shows how tags are reconstructed through the linking table. The write side shows how one new relationship row changes the visible page.

Check Your Understanding

1. What does one row in card_tags represent?
2. Why is a linking table needed here instead of adding one tag_id column directly to cards?
3. Where do card_id and tag_id come from when a tag is added to a card?

Programming Exercise

This chapter’s programming exercise builds on the finished Chapter 9 project state. Your task is to extend that project with a many-to-many feature by connecting existing tags to cards.

Loading Exercise...