Forum Discussion

RyanWhitener's avatar
RyanWhitener
Qrew Cadet
5 years ago

Many to Many Relationship to a Many Relationship?

So I am not sure how to fully label the item I am working through, so I will do my best to describe it.

I have a Jobs table (great grandparent) to a Packages table (grandparent) to a Breakers table (parent) to a Coordination (child). The particular case I am trying to wrap my head around is: Within the Package table, there is a field called "equipment type", one option is "panel" and another is a "transformer". Easy enough right, So now we have (2) records in the Package table. Well when "equipment type" is equal to "panel" a "breakers'" report table is accessible to that individual to begin listing the breakers within that panel. Still simple enough. Now we get complicated. I want to map out/describe where they breakers go. So I created this "Coordination" table and related it back to both the Package table AND the Breaker table. The reason being is I wanted to select a breaker and relate it to a separate piece of equipment. 

Example. (Package Table)Panel MP2 has a breaker named XXX (Breaker Table) which provides power to XFMR 2 (Package Table).

Now writing it out, I am wonder if I over complicated it and should just have (2) relationships between the Package Table and Breaker Table. The only extra piece to that is having it filter based on the Related Job I would be working in. So I can't select a "Panel" from the wrong job.

------------------------------
Ryan Whitener
------------------------------
Relationships
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    5 years ago
    Ryan,

    Without knowing how much business process follows after you have got a Job fully described it is hard to be sure of the right architecture.  However based upon what you said so far, the second option of two relationships is the easiest.  I would rename the reference fields in the Breakers Table to Related Panel and Related Load.   This way you explicitly know which of the two Parent Packages has a particular function for the Job.

    I am assuming that a particular Job has multiple packages

    • Job with RID 1, Job Name = Acme Rebuild
    • Package RID1, Package Name = Southside Panel
      • Related Equipment Type =1  (Panel)
      • Related Job =1
    • Package RID 2, Package Name = Air Compressor Isolation Transformer
      • Related Equipment Type = 2 (Transformers)
      • Related Job = 1
    • Breaker Table RID 1
      • Breaker Name = Siemens 100 Amp 3 Pole
      • Related Panel =1
      • Related Load =2


    To help avoid selecting the wrong Load you can use a Conditional Drop Down so that the
    •    Related Panel Related Job = Related Load Related Job
    This is not perfect as you can select any Package from that Job but at least you wont see choices from other Jobs.

    ​

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------