title: SQL - Tracking student exam records as they move between schools
body: <p>I'd like to pick some of your glorious minds for an optimal solution to my dilemma.</p>
  <p>Scenario:</p>
  <ul>
  <li>Schools have children and children take tests.</li>
  <li>The tests point to the child, not the school.</li>
  <li>If the child moves school, the test records are taken to the new school and the previous school has no record of the test being done as they are linked to the child.</li>
  </ul>
  <p>Obviously, this isn't ideal and is the result of the database not being designed with this in mind. What would the correct course of action be; I’ve currently identified the 3 possibilities listed below which would solve the current problem. However, i cannot be sure which is best for the issue at hand - and if any better solutions exist.</p>
  <ol>
  <li>Have each test store the school &amp; student within the test records (requiring current records to be updated &amp; increasing the size of the database)</li>
  <li>Create a new child record, duplicating the existing data for the new school with a new ID so the test remains linked to the previous school (complicating the ability to identify previous test scores)</li>
  <li>Separately keep track of moves to other schools, then use this additional table to identify current and previous using the timestamps (increased complexity and computational requirements)</li>
  </ol>
  <h1>EDIT:</h1>
  <p>So i tried to use a basic example, but requests for the task at hand have been requested.</p>
  <p>Here's the DB Schema for the tables (simplified for problem, note: Postnatal is not important):</p>
  <ol>
  <li>Patients: ID, MidwifeID, TeamID</li>
  <li>Midwives: ID</li>
  <li>Groups: ID</li>
  <li>GroupsMidwives: MidwifeID, GroupsID</li>
  <li>PatientObservations: ID, MidwifeID, PatientID</li>
  </ol>
  <p>Using a query as follows:</p>
  <pre><code>SELECT Some Information                                                                                                                                 
  from Postnatals                                                                                                     
  JOIN Midwives on Postnatals.MidwifeID = Midwives.ID                                                                                                                             
  JOIN Patients on Patients.PatientID = Postnatals.PatientID                                                                                                                          
  JOIN GroupsMidwives on GroupsMidwives.MidwifeID = Midwives.ID                                                                                                                           
  JOIN Groups on Groups.ID = GroupsMidwives.GroupID                                                                                                                           
  JOIN PatientObservations on PatientObservations.PatientID = 
  Postnatals.PatientID                                                                                                                            
  WHERE groups.Name = ?
  *some extra checks*                                                                                                                                                                                                                                         
  GROUP BY Midwives.Firstname, Midwives.Surname, Midwives.ID
  </code></pre>
  <p>However, in the event that a midwife is moved to a different team, the data associated with the previous team is now owned by the newly assigned team. As described in the example detailed previously.</p>
  <p>Thus a modification (which modification is yet to be realised) is required to make the data submitted - prior to a team change - assigned to the previous team, as of current, because of the way the records are owned by the midwife, this is not possible.</p>