One-to-One, One-to-Many, Many-to-Many

Build the mental model

One-to-one — User <-> Profile (exactly one row pairs with one row)
One-to-many — User -> many Orders/Posts (each belongs back to just one User)
Many-to-many — Students <-> Courses (via a join table, student_courses)

One-to-one means a single row in one table connects to exactly one row in another — a User and that user's Profile, kept separate for organization but always paired.

One-to-many means a single row connects to multiple rows elsewhere, but not the reverse — one User can have many Orders, yet each Order belongs back to exactly one User. This is the most common relationship shape.

Many-to-many means rows on both sides can connect to multiple rows on the other side — many Students take many Courses. Neither table can hold a single simple foreign key here, so a join table (student_courses) sits in between.

What a join table row is

Each row in student_courses is just a pair: one student_id and one course_id, representing a single enrollment. The relationship is really two one-to-many links meeting in the middle.

text

THREE RELATIONSHIP SHAPES
-------------------------
THREE RELATIONSHIP SHAPES
----------------------------
ONE-TO-ONE
  User  <----------------->  Profile

ONE-TO-MANY
  User  <---------------+---  Post
                         +---  Post
                         +---  Post

MANY-TO-MANY (via a join table)
  Student  <--->  student_courses  <--->  Course
  Student  <--->        |          <--->  Course
                        v
               (student_id, course_id) pairs

Connect it to a real scenario

Recognizing which relationship shape a real feature needs is one of the highest-leverage skills in data modeling. A quick test: ask 'can side A have more than one of side B, and vice versa?'

If both answers are no, it's one-to-one; if one side can have many, it's one-to-many; if both sides can have many, it's many-to-many and needs a join table — there's no way to model true many-to-many with a single foreign key.

The 'list in one column' signal

Any time you catch yourself wanting to put a list inside a single column, that's usually a sign you actually need a many-to-many relationship and a join table instead.

One-to-one: one row pairs with exactly one row elsewhere (User <-> Profile)
One-to-many: one row connects to many rows, each belonging back to just one (User -> Orders)
Many-to-many needs a join table in between (Students <-> student_courses <-> Courses)

Try the working example

javascript

const students = [
  { id: 1, name: "Aung" },
  { id: 2, name: "Moe" },
];

const courses = [
  { id: 10, title: "Databases 101" },
  { id: 20, title: "Web Basics" },
  { id: 30, title: "Networking" },
];

const studentCourses = [
  { student_id: 1, course_id: 10 },
  { student_id: 1, course_id: 20 },
  { student_id: 2, course_id: 20 },
  { student_id: 2, course_id: 30 },
];

function getEnrolledCourses(studentId, joinTable, coursesTable) {
  const courseIds = joinTable
    .filter((link) => link.student_id === studentId)
    .map((link) => link.course_id);
  return coursesTable.filter((course) => courseIds.includes(course.id)).map((c) => c.title);
}

console.log("Aung is enrolled in:", getEnrolledCourses(1, studentCourses, courses));
console.log("Moe is enrolled in:", getEnrolledCourses(2, studentCourses, courses));

You should see

Aung is enrolled in: [ 'Databases 101', 'Web Basics' ]
Moe is enrolled in: [ 'Web Basics', 'Networking' ]

5-minute try-it

Add a new enrollment to studentCourses (student 1 taking course 30 as well), predict how getEnrolledCourses(1, ...) changes, then run it to check.

One important caution

Trying to model many-to-many by storing a comma-separated list of ids in one column instead of using a join table

Assuming one-to-one always means the two tables should just be merged into one — sometimes yes, but not always (e.g. optional or sensitive data kept separate)

Wikipedia: Many-to-many (data model) — How Databases Work