title: Database design adding multiple item
body: <p>I am designing a database using MySQL Workbench for a clinic where each patient can see multiple doctors in each visit.</p>
  
  <p>The table of patient is:</p>
  
  <pre><code>patient_id
  
  patient_name
  
  patient_address
  
  patient_phone
  </code></pre>
  
  <p>The visit table:</p>
  
  <pre><code>visit_id
  
  patient_id
  
  doctor_id
  
  visit_result
  
  medicament_id
  </code></pre>
  
  <p>The problem is sometimes the patient in each visit may take multiple medicament in a prescription.</p>
  
  <p>How can I incorporate this in the design ?</p>
  
  <p>I tried to add all the medicament in the same line:</p>
  
  <blockquote>
    <p>Per example: 1, 4, 34.</p>
  </blockquote>
  
  <p>But I can't do an SQL query later to see how many patient were given the medicament of the respective ID.</p>
  
  <p>And the same thing if the patient saw multiple doctors in the same visit.</p>
  
  <p>Extra info:</p>
  
  <p><strong>medicament_table:</strong></p>
  
  <pre><code>medicament_id
  
  medicament_name
  
  medicament_expiry
  
  medicament_quantity
  </code></pre>