Getting Cardinality Right

Ask the question in both directions, out loud. Can one workshop have many signups? Can one signup belong to many workshops?

One yes and one no is many-to-one. Two yeses is many-to-many and needs its own table. Two noes is one-to-one, which is rarer than people think and usually means the two things are actually one thing.

Getting this wrong is the most expensive schema mistake, because fixing it later means migrating data rather than altering a column.

The join table earns its keep. A many-to-many join table is not overhead: it is where the facts about the relationship live.

When did they sign up? Did they attend? Did they cancel, and when? None of those belong on the person and none belong on the workshop. They are facts about the pairing, and the join table is the only honest home for them.

◈ Graded Query

The join table carries facts about the relationship. Return each person's name and how many workshops they are currently signed up for, counting only signups that have not been canceled, including people signed up for none, who must show 0. Two columns, most signups first, then name alphabetically.

Checked against the rows your query returns. 1 visible and 2 hidden checks, some against data you cannot see.

loading editor…

Mark this lesson complete

Signed in, your progress follows you to every device.

Next: Entity Relationship Diagrams →

← Back