Database many to many insert pattern

Question

Suppose I have a DB schema with two tables, a Student table and a Subject table. The relationship between these is many-to-many, so I also have a StudentSubject join table.

Now suppose that, periodically, there is a new subject and all students must be enrolled into this subject. Is there any sort of clever insert pattern for this?

My guess would be to insert the new subject into Subject, query for all Student IDs, and then insert n StudentSubject records for each StudentID and the new SubjectID. This doesn't feel very efficient as I have to query a table, insert into one table, and then do multiple inserts into another table.

Would this be more efficient in a noSQL Schema? For example, I have a Students document and each student Item has a subjects attribute. I could loop through each Student item and append to subjects. Would this be more scalable?

Answer 1

Have you considered INSERT INTO SELECT (https://www.w3schools.com/sql/sql_insert_into_select.asp)?

Example assuming 123 is the id of the newly inserted Subject, and that student_id is used as primary key in Student table, and as foreign key in StudentSubject table:

INSERT INTO StudentSubject SELECT student_id, 123 as subject_id FROM Student;

Answer 2

Maybe this doesn't really answer your question directly, but I personally would not try to maintain explicit mappings for each individual Student ID for subjects that every student must be enrolled on. I might choose to have a separate table for those subjects and handle that at the application layer, otherwise you have to do a huge bulk update every time a subject is added and that won't scale well as more students join.

So I'd maybe do 2 separate queries when finding subjects a given student is enrolled in. One for all the mandatory subjects and one for optional ones which that specific student might be enrolled on. That's where I would use the join table.

Not a direct answer to the question but more so a different way of looking at the problem.

• cluelessatthis: Thanks for your reply! What happens if I want to store extra info regarding each relationship? Say each student has a grade for each subject, I would have thought the place to store this would be inside StudentSubject, but now there's no record inside StudentSubject for all the mandatory subjects
• LikeLikeAteMyShield: For this I would possibly have a separate table just for grades. Subjects a student is enrolled in and grades for a subject are separate things as at the start of term a student won't have any grades but they will be enrolled in all their subjects. This still scales OK since you can just insert grades as they are achieved without needing bulk inserts.