• Created by Unknown User (mritzing), last updated on Jul 18, 2018 1 minute read

Explanation of a few types of table relations, one-to-one, many-to-one, and many-to-many, and how to set up SQL tables for those relations

One-to-One:

Use a foreign key to the referenced table:

one-to-one
student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id # you can have a link back if you need

One student per address, one address per student

Many-to-One:

Use a foreign key on the many side to link back to the one side:

many-to-one
teachers: teacher_id, first_name, last_name # the one side
classes:  class_id, class_name, teacher_id  # the many side

One teacher per class, many classes per teacher

Many-to-Many:

Use a junction table (shown below):
many-to-many
student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id     # the junction table

Many students per class, each student is taking many classes

junction table
students:
id | first  | last
=====================
1  | John   | Lee
2  | Jane   | Wilson
3  | Daniel | Gomez

classes:
id | name    | teacher_id
==========================
1  | Biology | 2
2  | Physics | 4
3  | English | 77

student_classes
s_id | c_id
======================
  1  |  2   # John is taking Physics 
  1  |  3   # John is taking English
  2  |  2   # Jane is taking Physics
  3  |  1   # Daniel is taking Biology