Relationships: one-to-one, one-to-many, many-to-many

A relationship links two entities, such as students and courses. Its cardinality decides which tables you need.

Each student has one login for the online enlistment system. Searching for a course lists its sections, each with a schedule and open slots. Enlisting in one section adds it to your schedule, and many other students do the same.

Student and login accountOne-to-one
Course and its sectionsOne-to-many
Students and coursesMany-to-many
Think first. Each student has one account, each course has several sections, and many students take many courses. How would you sketch tables for these three facts?
Ask students to sketch the tables on paper before they open the next step. Most put a list of student ids in the course row, which a table column cannot hold.

Open each tab to see the tables that type needs. A table holds one entity. A primary key (pk) identifies each row, and a foreign key (fk) points to a row in another table.

More detail

What this type means for your tables

Neither the students table nor the courses table can hold the other's key, because each row would have to point at many rows. The extra table has one row for each pairing of a student and a course. It is also the only place for a date, a status or a grade, because those facts describe the pairing.

You can now choose the tables and keys for any relationship.

More detail

Entities and attributes treats Enlistment as an entity for the same reason. A many-to-many link on a diagram is a sign that a table is missing.

If you choose the wrong type on the diagram, you find out later, when somebody needs a second value in a place that allows only one.