I have this database I want to use as a backend for a teacher resource database.
The columns in question are as follows:
In this table, a class has one subject, one course number, and one section. This class can be taught by many instructors, but an instructor can only teach one class. The issue is there are not enough unique identifiers for a teacher, which I want to base my database on. I just want to prevent redundancy in the database at all possible. Ideally, I would like someone to look up a teacher, and then have that teacher's name link to all the classes they teach.
The tricky part is that while teacher teaches one class, a class can have the same yearterm, the same subject, and the same course number. Although grouping them all together along with a section number produces a unique row.
I was thinking about just making a list of names, with unique number IDs, and then just referencing it as a foreign key to all the classes.
What would be an appropriate way to set up the primary keys for this database? Additionally, what would the relationship be between teacher and class?