3 Replies Latest reply on Mar 8, 2010 1:29 PM by comment_1

    Relationship question



      Relationship question


      Hi all, I've been working on a database for an ag consulting firm and have been asked to revise my database design due to a misunderstanding on my part.  I would appreciate opinions on how I should proceed given the following scenario:


      The database has (at the moment) three (3) related tables: Livestock Sites ('Sites'), Livestock Operations ('Operations'), and Manure Storage Sites ('Storage').  I initially thought the relationship was from Sites-->Operations-->Storage, where each subsequent table would be nested within the higher-order category (i.e., table).  However, it turns out that for a given Site, there can be multiple Storage sites which accommodate the excrement from potentially many Operations, with each Operation potentially associated with more than one Storage.  For example, on Site 1, there may be Storages A and B, and Operation I filling Storage A, Operation II filling Storage B, and Operation III filling Storages A and B.  Ultimately, data need to be summarized according to Storages for a given Site, so in my mind, it makes sense to organize the tables: Sites-->Storage-->Operations.  Does that make sense?  Are there any problems that can be foreseen from this scenario?


      Thanks as always for the input.



        • 1. Re: Relationship question

          Can one Storage serve more than one Site? If not, it seems you have:


          Storage >- Sites -< Operations -< JoinTable >- Storage


          You didn't say what exactly needs to be tracked here, so the join table might not be strictly necessary - but you are certainly describing a many-to-many relationship between Operations and Storage.

          • 2. Re: Relationship question

            Thanks for the comment.


            Fortunately, Storage vessels are limited to the Site on which they're found; a Storage vessel cannot service other Sites. 


            Can you please describe what happens in a Join Table?  I believe this is where the links (in this case) between Storage and Operations are made, but how would I go about doing that?  I know the fields would include: Site_ID, Storage_ID, Operation_ID, but how the fields be completed?  Manually?  Any clarification would be helpful because it just might suit my needs.

            • 3. Re: Relationship question


              k1ngf1sher wrote:

              I know the fields would include: Site_ID, Storage_ID, Operation_ID

              The SiteID field is redundant, since the site is known from the operation record. There would also be fields to describe the unique join (I don't know what they might be - perhaps quantity?).


              For a basic demo of a join table, see: