Composite Keys refering to multiple tables
Is it possible to set up a composite key for a table that can be referenced correctly in more than one table.
For instance, I have two tables with unique sets of data, tblStudents and tblTeachers. In each table I have a Notes field, and as there can be more than one note for each entity, this is set up as a separate table using a composite key "NoteID + StudentID" or "NoteID + TeacherID".
In this example, I would need two separate tables, tblStudentNotes and tblTeacherNotes. Is it possible to merge these two tables to tblNotes and then have the following fields:
With the three ID fields as a composite key?
The theory sounds easy enough, but how do I reference the table name/id into TableID?