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?