I am developing a database and having a bit of trouble understanding how to represent a specific situation. Firstly, this is an archaeological database. There are contexts (where an artifact might be found), bags in which the the artifacts are stored, and the artifacts themselves. A single context may have many bags in which the artifacts are stored, and a single bag may have artifacts from many contexts. Additionally, a single bag may have many artifacts, but an artifact may only be present in a single bag. Each of these (context, bag, artifact) is a table in the database. The current structure is as follows: context - bag (many-to-many) and bag - artifact (one-to-many).
The problem comes when attempting to distinguish the context of a single artifact.
For instance, if a bag has two artifacts, one from context 1 and the other from context 2, how do I select a specific context for a specific artifact?
Currently, when I relate an artifact to a bag (with multiple contexts) via a foreign key, the context of the artifact simply displays as the first context that was joined to the bag.
Any insight would be greatly appreciated.