Data in Multiple Tables

Relational Queries in Applications

Learning Objectives

  • You can explain how a one-to-many relationship becomes a visible application feature.
  • You can write routes that load one deck together with the cards that belong to it.
  • You can add a new card to one chosen deck through a form.

So far in this part, the focus has been on relational SQL itself. That raises a practical next question: how does a relationship between tables become something a user can actually use in an application?

In this chapter, the same study tracker project grows from one-table deck pages toward a small relational feature. A deck can contain many cards, and the application should be able to show one chosen deck together with its cards.

The Cards Table

To add cards to the project, the schema needs one new table.

CREATE TABLE cards (
  id SERIAL PRIMARY KEY,
  deck_id INTEGER NOT NULL REFERENCES decks(id),
  question TEXT NOT NULL,
  answer TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

The important column here is deck_id.

That column connects one card row to one deck row. In ordinary language, it means that this card belongs to this deck. That is the one-to-many relationship in the project: one deck can have many cards, but each card belongs to one deck.

The REFERENCES decks(id) part is a foreign key constraint. It tells the database that deck_id must always point to an existing row in decks. We will look into the syntax for foreign key constraints more carefully later; for now, the important reading is that the constraint makes the relationship between the two tables explicit.

By this point, the project should already include the earlier tags migration. In this chapter’s project work, add the cards table through a new migration file called 004_cards.sql.

It is also practical to insert a few seed card rows in that same migration so that the new deck detail page has visible content immediately after the migrations run.

Loading Exercise...

Showing One Deck with Its Cards

Once the cards table exists, the next useful page is a deck detail page. That page should show one chosen deck and the cards that belong to it.

A single JOIN query could combine the two tables, but it would repeat the deck information in every result row — once for each card. The deck-detail page, however, has two visible parts with different shapes: one deck (name, description) at the top, and then a list of cards below. It is easier to match the query shape to the page shape by using two smaller queries: one fetchone() for the deck and one fetchall() for its cards.

One route can do that in two small steps. First load the chosen deck, and then load the cards whose deck_id points to that deck.

@app.get("/decks/{deck_id}")
def show_deck(request: Request, deck_id: int):
    with get_connection() as conn:
        deck = conn.execute(t"""
            SELECT id, name, description
            FROM decks
            WHERE id = {deck_id}
            """).fetchone()

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

        cards = conn.execute(t"""
            SELECT id, question, answer
            FROM cards
            WHERE deck_id = {deck_id}
            ORDER BY id
            """).fetchall()

    return render(request, "deck_detail.html", deck=deck, cards=cards)

This is still a relational feature even though the page uses two queries instead of one join. The second query only makes sense because the relationship between decks.id and cards.deck_id exists: the route is using that relationship to fetch the cards that belong to this chosen deck.

Notice also that the deck is loaded first, and the route returns early if no such deck exists. That way, the cards query does not run for a deck that is not there, and the template never has to handle a missing deck.

A good question to ask after reading the route is what one row in cards represents here. The answer is: one card that belongs to the chosen deck.

That interpretation keeps the query and the page behavior connected.

Rendering the Deck Detail Page

The matching template can show both the deck information and the list of cards.

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

<!doctype html>
<html lang="en">
  <body>
    <h1>{{ deck.name }}</h1>

    {% if deck.description %}
      <p>{{ deck.description }}</p>
    {% endif %}

    <h2>Cards</h2>

    {% if cards %}
      <ul>
        {% for card in cards %}
          <li>
            <strong>{{ card.question }}</strong>
            - {{ card.answer }}
          </li>
        {% endfor %}
      </ul>
    {% else %}
      <p>No cards yet.</p>
    {% endif %}
  </body>
</html>

The template is not finding the related rows by itself. The route already did that. The template is only rendering the deck and cards values that it received. In other words, the route owns the database work, and the template owns the visible page structure.

The {% if cards %} branch is what the two-query design pays off for: an empty list of cards renders as a simple “No cards yet.” message, because cards is just an empty list, not a NULL from an outer-joined row.

Loading Exercise...

Linking to the Deck Page

If the application has a deck detail page, the user should have a clear way to open it.

One simple approach is to add a View deck link to each deck row on /decks.

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

{% for deck in decks %}
  <li>
    {{ deck.name }}
    <a href="/decks/{{ deck.id }}">View deck</a>
  </li>
{% endfor %}

That link is useful for two reasons: it gives the user a visible path to the relational page, and it makes the deck_id in the URL concrete instead of abstract.

Loading Exercise...

Adding a Card to One Chosen Deck

The next natural question is how a user adds a new card to this chosen deck.

The simplest answer is a form on the deck detail page.

This form belongs in app/templates/deck_detail.html, below the card list:

<h2>Add card</h2>
<form action="/decks/{{ deck.id }}/cards" method="post">
  <label for="question">Question</label>
  <input id="question" name="question" type="text" />

  <label for="answer">Answer</label>
  <textarea id="answer" name="answer"></textarea>

  <button type="submit">Add card</button>
</form>

This form is worth reading carefully. The URL already contains deck.id, so the chosen deck comes from the path, while the new card content comes from the form fields. That separation is one of the important application patterns in this chapter.

The Route That Creates the Card

The matching route can then read the deck identifier from the path and the card content from the form.

@app.post("/decks/{deck_id}/cards")
def create_card(
    deck_id: int,
    question: str = Form(...),
    answer: str = Form(...),
):
    with get_connection() as conn:
        conn.execute(t"""
            INSERT INTO cards (deck_id, question, answer)
            VALUES ({deck_id}, {question}, {answer})
            """)

    return RedirectResponse(f"/decks/{deck_id}", status_code=303)

That route can be read in small steps. It receives the chosen deck_id from the URL, receives question and answer from the form, inserts a new row into cards, stores the chosen deck through deck_id, and then redirects back to the same deck page.

The insert uses the parameterized t-string form introduced in the previous part. The three interpolated values — deck_id, question, and answer — are all passed as data, not as SQL text.

This is the point where the one-to-many relationship becomes concrete in the application. Adding a card to a deck really means:

  • create a cards row whose deck_id points to the chosen deck.
Loading Exercise...

What to Check

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

  • does each deck row on /decks include a View deck link?
  • does /decks/{deck_id} show the chosen deck and only its cards?
  • does the page show No cards yet. when no related rows exist yet?
  • after submitting the form, does the new card appear on the same deck page?
  • if you open another deck, is the new card absent there?

Those checks help verify both sides of the feature:

  • the read side, where related rows are shown on the correct page,
  • and the create side, where a new related row is attached to the correct deck.

Check Your Understanding

  1. What does cards.deck_id mean in ordinary language?
  2. Why does /decks/{deck_id} use two queries instead of one join here?
  3. Where do deck_id, question, and answer come from when a new card is created?

Programming Exercise

This chapter’s programming exercise builds on the project checkpoint from the previous part. Your task is to extend that existing deck CRUD project with a first relational feature: cards that belong to decks.

Loading Exercise...
← A Relational-Algebra Reading of Queries
Tags for Cards in Applications →