Modeling one-to-many versus many-to-many relationships

WHAT THIS TESTS The interviewer is checking foundational data-modeling instincts: recognizing cardinality and applying the junction-table idiom rather than denormalized hacks.

A GOOD ANSWER COVERS A one-to-many relationship means one parent row relates to many child rows, but each child belongs to one parent. You model it by putting a foreign key on the many side pointing to the one side, for example each Order row holding a CustomerID. A many-to-many relationship means each row on either side can relate to many rows on the other; a student takes many courses and a course enrolls many students. A plain relational table cannot store this with a single foreign key, so you add a junction table, often called Enrollments, with StudentID and CourseID foreign keys. The composite primary key (StudentID, CourseID) enforces uniqueness of each pairing, and the junction can carry extra attributes like enrollment_date or grade.

COMMON WRONG ANSWERS Storing a comma-separated list of course IDs in a column, which breaks 1NF and prevents joins, indexing, and integrity. Adding a CourseID directly to Students, which only allows one course per student. Forgetting the composite primary key, allowing duplicate enrollments.

LIKELY FOLLOW-UPS Where do you put relationship attributes like grade? How do you query all courses for a student? Why is the comma-separated approach harmful for indexing and referential integrity?

ONE CONCRETE EXAMPLE Students(StudentID PK, Name), Courses(CourseID PK, Title), and Enrollments(StudentID FK, CourseID FK, Grade) with primary key (StudentID, CourseID). Finding every course a student takes is a join from Students through Enrollments to Courses, and the composite key guarantees a student cannot be enrolled twice in the same course.