2 Replies Latest reply on Mar 12, 2017 3:59 AM by keywords

    Data structure issue - Archaeological database




      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.

        • 1. Re: Data structure issue - Archaeological database

          What you are saying, would be true if you are showing one related context record from the artifact table. If you want to show multiple context records from the artifact record, you would need to show the related contexts in a portal from the artifact table the many to many "bag" table... does this make sense?

          • 2. Re: Data structure issue - Archaeological database

            Because the context – bag link is many-to-many you will have to insert a join table to resolve this. The join table functions to break down the many-to-many relationship into two one-to-many relationships: Context—JOIN—Bag.

            The join table only needs to have a foreign key field for context and a foreign key field for bag; these are used to make each side of the link—to the primary key field respectively in each of the other tables.

            Thus context 1 will have a join to bag A, and context 2 will have a separate join to bag A. From bag A you will be able to see both contexts, and from each context you will see bag A without either affecting the other.