Skip to the content
Start

Project: a club's database

SQL · Unit 12: Designing tables

A swim club keeps its accounts in a notebook, and at the end of the year nobody knows who owes what. The treasurer wants a database, and building it from scratch is on you. Its two tables are new. They have nothing to do with the company database that sits in the background.

Before you write a CREATE TABLE, it helps to say the rules in plain English. These are the treasurer's. A member has a name, always. No two members share an email. Dues are 10 a month, unless a different amount is agreed on. They're never negative, and never left blank. A payment comes from a member who exists and covers one month, and nobody pays twice for the same month.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Write the code

    First the members, because the payments are going to point to them. Create the members table, with id, name, email, and dues. The id is the usual id INTEGER PRIMARY KEY. The other rules are the treasurer's. A member has a name, always. No two members share an email. Dues are 10 unless a different amount is agreed on, never negative, and never left blank. To check it, the app tries to put seven rows in, and only three follow the rules.

  2. 2. Write the code

    Now the payments. The members table is already written. Below it, create payments with three columns, in this order. member_id is a required integer that points to the id of members. month is required text, written like '2026-01'. amount is a required number with decimals. Nobody pays twice for the same month, so the primary key is member_id and month together, in that order. Write the types as INTEGER, TEXT, and REAL.

  3. 3. Multiple choice

    The treasurer moves the notebook into the database, and the notebook has a bit of everything. Both tables are built. Abby is member 1 and has already paid for January. Gabe is member 2. Which of these does the database let you store, even though it isn't true?

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →