I have a RDBMS design question and would like to seek help from various DB gurus~
I am storing some information about Teachers, so I created a table called 'Teacher' PK [TeacherID int].
For each Teacher, I also need to store their [Subject] they teach, their [Grade] they teach, and among others.
My dilemma is whether I need to declare additional tables to accomodate the 1-to-many relationship between Teacher-Subject and Teacher-Grade. I thought about declaring a new table called 'Subject' PK [SubjectID int] with column [SubjectName varchar], and in addition declare a junction table 'J_TeacherSubject' PK [TeacherID int, SubjectID int] to denote teachers can teach multiple subjects. Pros: Clean and easy to add new subjects. Cons: Slightly more complex queries.
Another solution I find is instead of creating new tables, I declare the column [Subject] of 'Teacher' to be varchar, then concatenate multiple subjects into a string, eg. ';math;sci;' I can now use SQL, LIKE '%;math;%' to find all teachers that teach math. Pros: Simple queries. Cons: Perhaps not the idea of a RDBMS.
I know that the decision may lie under num of rows in 'Teacher' I am talking about. I expect the max num of teachers to be less than 100,000 with MS SQL Server as the backend... Please help, DB gurus, I have a lot of tables that are like such needs to denote a 1-to-many relationship and would love to hear suggestions from you!