Hi friends,

I am a little confused with some basic basics.

I am making a database where I have tables that I want to discuss;
Subjects
SubjectsLkp
BwSubjectsTaught
Teachers


Business Logic:
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?

Thankyou

OxyJen

no, there should be no foreign keys in the teacher table

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

"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
eg BwSubjectsTaughtCoK
Do I need to also add separate relationships in there eg
BwSubjectsFK
BwTaughtFK
or does the composite handle the relationship for you?

sorry for the delay in replying, my computer died and i had to wait for the new one to be shipped

if you would like more help with your keys, could yo uplease show the columns in each table and what they're for...