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
Use a 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 |
One student per address, one address per student
Use a foreign key on the many side to link 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 |
One teacher per class, many classes per teacher
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
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 |
Related articles appear here based on the labels you select. Click to edit the macro and add or change labels.
|