I am making a database where I have tables that I want to discuss;
1) I want teachers to be able to select subjects and their description from a subject listing. I have put these subjects (names and descriptions) in columns in a lookup (descriptive) table. Its called SubjectsLkp.
2) I want to be able to sort teachers according to the subjects they teach. There are many teachers and many subjects. A many to many relationship is normalised via a bridging table called BwSubjectsTaught.
I want to clarify my foreign keys in the teachers table;
Do I add foreign key (it would be a lookup value for SubjectsLkp) assuming that one record for a teacher gives one subject name?
Do I add a foreign key which references a bridging table so that I can sort subjects against Subjects via BwSubjectsTaught?
There is a distinct difference in the methodology between lookup (descriptive) tables and the normalisation of many to many relationships, and the use of foreign keys would be different in the two cases?
"there should be one foreign key in the BwSubjectsTaught table that references the teacher table, and another that references the subjects table
together these two foreign keys will comprise the composite primary key of the BwSubjectsTaught table"
I have the composite key already in the bridging table
It does comprise of foreign keys and has the suffix CoK
Do I need to also add separate relationships in there eg
or does the composite handle the relationship for you?