Data structure issue - Archaeological database

Discussion created by MichaelLyons on Mar 12, 2017
Latest reply on Mar 12, 2017 by keywords



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.