0 votes
1 view
in SQL by (20.3k points)

Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?

1 Answer

0 votes
by (40.4k points)
  • For One-to-one, you can use the foreign key to the referenced table:

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

You can also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (student).

  • For One-to-many, you can use the foreign key on the many side of the relationship linking back to the "one" side:

teachers: teacher_id, first_name, last_name # the "one" side

classes:  class_id, class_name, teacher_id  # the "many" side

  • For Many-to-many, you can use a junction table :

As for example:

student: student_id, first_name, last_name

classes: class_id, name, teacher_id

student_classes: class_id, student_id     # the junction table

Examples of queries:

 -- For getting all students for a class:

  SELECT s.student_id, last_name

      FROM student_classes sc 

INNER JOIN students s ON s.student_id = sc.student_id

     WHERE sc.class_id = X

-- For getting all classes for a student: 

   SELECT c.class_id, name

      FROM student_classes sc 

INNER JOIN classes c ON c.class_id = sc.class_id

     WHERE sc.student_id = Y

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !