When dealing with `many-to-many` relations in MySQL using Meteor

help
1

Please consider this as part of the When I need to use MySQL in production - #3 by maxhodges

I made separate topic by making a specific question.

Let’s see a classic many-to-many relations as below with a join table called teacher_student.

I have 2 questions:

  1. Can this relation be ported into Mongo ? AFAIK, when it comes to many-to-many relation, the only way is to deal with RDBMS?

  2. If I will want to use this in MySQL, is it possible to deal with this schema and keep the reactiveness feature of Meteor ?

  3. I will need create a RESTful APIs, with the URLs:

  • <url>/api/teachers/ - the list of all teachers
  • <url>/api/teachers/:teacher_id/students/ - the list of all students for this given teacher
  • <url>/api/students/ - the list of all students
  • <url>/api/students/:student_id/teachers - the list of all teachers for this given student

In particular I’m interested in GitHub - kahmali/meteor-restivus: REST APIs for the Best of Us! - A Meteor 0.9+ package for building REST APIs https://atmospherejs.com/nimble/restivus by @kahmali

I’m new these things, so please correct if any of my statements/questions are not correct. :wink:

school.sql:

DROP DATABASE IF EXISTS `school`;
CREATE DATABASE `school`;
USE `school`;

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `teacher_student`;
CREATE TABLE `teacher_student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `student_id` int(11) unsigned NOT NULL,
  `teacher_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `teacher_id` (`teacher_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `teacher_student_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON DELETE CASCADE,
  CONSTRAINT `teacher_student_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `student`(`id`,`name`) values (1, 'student 1');
INSERT INTO `student`(`id`,`name`) values (2, 'student 2');
INSERT INTO `student`(`id`,`name`) values (3, 'student 3');
INSERT INTO `student`(`id`,`name`) values (4, 'student 4');
INSERT INTO `student`(`id`,`name`) values (5, 'student 5');

INSERT INTO `teacher`(`id`,`name`) values (1, 'teacher 1');
INSERT INTO `teacher`(`id`,`name`) values (2, 'teacher 2');
INSERT INTO `teacher`(`id`,`name`) values (3, 'teacher 3');
INSERT INTO `teacher`(`id`,`name`) values (4, 'teacher 4');
INSERT INTO `teacher`(`id`,`name`) values (5, 'teacher 5');

INSERT INTO `teacher_student`(`teacher_id`,`student_id`) values (1, 1);
INSERT INTO `teacher_student`(`teacher_id`,`student_id`) values (1, 2);
INSERT INTO `teacher_student`(`teacher_id`,`student_id`) values (1, 3);

INSERT INTO `teacher_student`(`teacher_id`,`student_id`) values (2, 1);
INSERT INTO `teacher_student`(`teacher_id`,`student_id`) values (2, 2);
INSERT INTO `teacher_student`(`teacher_id`,`student_id`) values (2, 3);

INSERT INTO `teacher_student`(`teacher_id`,`student_id`) values (3, 1);
INSERT INTO `teacher_student`(`teacher_id`,`student_id`) values (3, 2);
INSERT INTO `teacher_student`(`teacher_id`,`student_id`) values (3, 3);
2

In terms of your model, perhaps you should introduce a “class” or “course” collection. Teachers and Students are only associated with each other in the limited context of a Course aren’t they?

  • Students
  • Teachers
  • Courses

So a Course doc would have a teacherId and an array of studentIds and perhaps a startDate and endDate too? You could also have an array of teachers if a class may have more than one.

Q. But what if I delete a teacher or student? The class record will be orphaned?

A. Think about a Course as point-in-time data. Just because a teacher quit the organization doesn’t mean the class never happened. So you’d probably don’t really want cascade delete and instead want to a soft delete on the Teacher record (i.e. termination date).

1 Like