title: Data schema for complex entities - which approach is simpler
body: <p>I am reviewing a database model. I need some help with a part of the model. At this stage I'm just concerned with the logical model, not the implementation. I want to adopt a best practice.  </p>
  
  <p><strong>Summary of problem</strong></p>
  
  <ul>
  <li><p>The database is used by an app that manages legal cases for a law firm.  </p></li>
  <li><p>Each case has multiple parties. (By party, I mean a some legal enity in the real world that has a stake in the case.)</p></li>
  <li><p>There are about 40 different types of parties.</p></li>
  <li><p>2 of these types can be a single person, a single organisation, or multiple persons and/or organisations joined together in any combination. </p></li>
  <li><p>The other 38 types can be either a single person or a single organisation.</p></li>
  <li><p>Each case always has 2 parties of the complex types (i.e. potentially a combination of persons and organisations).</p></li>
  <li><p>Usually there are 5 to 10 parties in total per case.</p></li>
  </ul>
  
  <p><strong>Options</strong></p>
  
  <ol>
  <li><p>Each party is modelled as potentially a combintation of any number of persons and organisations. The tables would look like this:</p>
  
  <ul>
  <li>Case <code>&lt;</code>- CasePartyAssignment -<code>&gt;</code> Party<br>
  Where all parties are potentially a combination of persons and organisations:</li>
  <li>Party <code>&lt;-</code> PartyPersonAssignment <code>-&gt;</code> Person</li>
  <li>Party <code>&lt;-</code> PartyOrgAssignment <code>-&gt;</code> Organisation  </li>
  </ul></li>
  <li><p>Alternatively, I model this with 3 different types CasePartyAssignment tables.</p>
  
  <p>The first is the same as 1 above, which covers the complex scenario:</p>
  
  <ul>
  <li><p>Case <code>&lt;-</code> CaseComplexPartyAssignment <code>-&gt;</code> ComplexParty  </p>
  
  <p>In addition I add specific tables for the simple scenarios:</p></li>
  <li><p>Case <code>&lt;-</code> CasePersonAssignment <code>-&gt;</code> Person</p></li>
  <li>Case <code>&lt;-</code> CaseOrgAssignment <code>-&gt;</code> Organisation</li>
  </ul></li>
  </ol>
  
  <p>The way I see it, both options have advantages and disadvantages. For example, in option 1 I create a single way to store the data, which in itself is simple due to consistency. But that means I also model a party that I know is a simple Person using the PartyPersonAssignment designed to model the complex party.</p>
  
  <p>Does anyone have any commnts/opinions about these options?</p>