title: SQLite/C#: Design a entity which is a child of many parent entities
body: <p>I have these relations:</p>
  
  <pre><code>1 Period has N Documents
  1 Pupil has N Documents
  1 Report has N Documents
  </code></pre>
  
  <p>These are my C# entities:</p>
  
  <pre><code>class Period
  {
     public int PeriodId {get;set;}   
     public List&lt;Document&gt; Documents {get;private set;}
  }
  
  class Pupil
  {
     public int PupilId {get;set;}   
     public List&lt;Document&gt; Documents {get;private set;}
  }
  
  class Period
  {
     public int ReportId {get;set;}  
     public List&lt;Document&gt; Documents {get;private set;} 
  
  }
  
  class Document
  {
     public int DocumentId {get;set;}
  
     // ??? 
     public int PeriodId {get;set;}
     public int PupilId {get;set;}
     public int ReportId {get;set;} 
  
     // ??? OR just a general Foreign-Key Id like
     public int Id_FK {get;set;}
  }
  </code></pre>
  
  <p>The problem is this:</p>
  
  <p>When I have a Document with the PeriodId 33 there are chances that I could also have a PupilId 33 because both tables use primary key/autoincrement. So there could be a pupil AND a period having the Id 33. Doing now an INNER JOIN on both tables I would return 2 documents from my sqlite query although I should get ONLY <strong>ONE</strong>.</p>
  
  <p>How would you solve that?</p>