I am building a database to allow teachers to collaborate and share their course materials. Many of the courses will include multiple items; presentation, hand-outs, quizzes etc. The goal is to allow them to “search a topic”, and then once they find something they are looking for, see any coursework which may be specifically related to that course that record is related to.
This, I thought, was a relatively straight forward project; create a table which captures the course information and stores the course work in a container, then use a multi-key relationship and second instance of the same table to display the related records in a portal – no sweat.
If I enter a presentation and a quiz, and from the record containing the presentation I add to the multi-key to show the relationship with the quiz, from the presentation record the quiz is shown as related. However, if I am on the quiz record, I don’t see the related presentation because the multi-key in the quiz record has not been populated. The same problem persists even using a join table.
Since I have a portal showing the related records, as they relate a record, I could use script to gather all the related keys in the “populated” multi-key, and then loop through each related portal row updating each related record’s multi-key. This would allow the user to see from any record, any other related records, regardless of where the relationship was established. However, this seems somewhat frail is there a better approach?