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.