title: Design : multiple visits per patient
body: <p><img src="https://i.stack.imgur.com/DHepv.jpg" alt="enter image description here"></p>
  
  <p>Above is my schema.  What you can't see in tblPatientVisits is the foreign key from <code>tblPatient</code>, which is <code>patientid</code>.</p>
  
  <p><code>tblPatient</code> contains a distinct copies of each patient in the dataset as well as their gender.  <code>tblPatientVists</code> contains their demographic information, where they lived at time of admission and which hospital they went to.  I chose to put that information into a separate table because it changes throughout the data (a person can move from one visit to the next and go to a different hospital).  </p>
  
  <p>I don't get any strange numbers with my queries until I add tblPatientVisits.  There are just under one millions claims in <code>tblClaims</code>, but when I add <code>tblPatientVisits</code> so I can check out where that person was from, it returns over million.  I thinkthis is due to the fact that in <code>tblPatientVisits</code> the same patientID shows up more than once (due to the fact that they had different admission/dischargedates).</p>
  
  <p>For the life of me I can't see where this is incorrect design, nor do I know how to rectify it beyond doing one query with <code>count(tblPatientVisits.PatientID=1</code> and then <code>union</code> with <code>count(tblPatientVisits.patientid)&gt;1</code>.  </p>
  
  <p>Any insight into this type of design, or how I might more elegantly find a way to get the claimType from <code>tblClaims</code> to give me the correct number of rows with I associate a claim ID with a patientID?</p>
  
  <p>EDIT: The biggest problem I'm having is the fact that if I include the <code>admissionDate,dischargeDate</code> or the <code>patientState</code>in the tblPatient table I can't use the patientID as a primary key.  </p>
  
  <p>It should be noted that <code>tblClaims</code> are <code>NOT</code> necessarily related to <code>tblPatientVisits.admissionDate, tblPatientVisits.dischargeDate</code>. </p>
  
  <p>EDIT:  sample queries to show that when tblPatientVisits is added, more rows are returned than claims</p>
  
  <pre><code>SELECT     tblclaims.id, tblClaims.claimType
  FROM         tblClaims INNER JOIN
                        tblPatientClaims ON tblClaims.id = tblPatientClaims.id INNER JOIN
                        tblPatient ON tblPatientClaims.patientid = tblPatient.patientID INNER JOIN
                        tblPatientVisits ON tblPatient.patientID = tblPatientVisits.patientID
  </code></pre>
  
  <p>more than one million query rows returned</p>
  
  <pre><code>SELECT     tblClaims.id, tblPatient.patientID
  FROM         tblClaims INNER JOIN
                        tblPatientClaims ON tblClaims.id = tblPatientClaims.id INNER JOIN
                        tblPatient ON tblPatientClaims.patientid = tblPatient.patientID
  </code></pre>
  
  <p>less than one million query rows returned</p>
  
  <p><img src="https://i.stack.imgur.com/iq3lJ.jpg" alt="sample rows"></p>
  
  <p><img src="https://i.stack.imgur.com/T4VVI.jpg" alt="more than one million rows"></p>