title: Split Table into many to many relationship: Data Migration
body: <p>I am wondering how best to migrate my data when splitting a Table into a many to many relationship. I've made a simplified example and I'll also post some of the solutions I have come up with.
  I am using a Postgresql Database.</p>
  
  <p><strong>Before Migration</strong></p>
  
  <p>Table Person</p>
  
  <pre><code>
  ID       Name        Pet        PetName
  1        Follett     Cat        Garfield
  2        Rowling     Hamster    Furry
  3        Martin      Cat        Tom
  4        Cage        Cat        Tom
  </code></pre>
  
  <p><strong>After Migration</strong></p>
  
  <p>Table Person</p>
  
  <pre><code>
  ID       Name
  1        Follett
  2        Rowling
  3        Martin
  4        Cage
  </code></pre>
  
  <p>Table Pet</p>
  
  <pre><code>
  ID       Pet        PetName
  6        Cat        Garfield
  7        Hamster    Furry
  8        Cat        Tom
  9        Cat        Tom
  </code></pre>
  
  <p>Table PersonPet</p>
  
  <pre><code>
  FK_Person     FK_Pet
  1             6
  2             7
  3             8
  4             9
  </code></pre>
  
  <p>Notes:</p>
  
  <ul>
  <li>I will specifically duplicate entries in the Pet Table (because in my case - due to other related data - one of them might still be editable by the customer while the other might not).</li>
  <li>There is no column that uniquely identifies a "Pet" record.</li>
  <li>For me it does not matter whether 3-8 and 4-9 are linked in the PersonPet table or 3-9 and 4-8.</li>
  <li>Also I omitted all code that handles the schema changes of the tables as this is - in my understanding - irrelevant for this question.</li>
  </ul>
  
  <p><strong>My Solutions</strong></p>
  
  <ol>
  <li>When creating the Pet Table temporarily add a column containing the id of the Person Table that was used to create this entry.</li>
  </ol>
  
  <pre><code>
      ALTER TABLE Pet ADD COLUMN IdPerson INTEGER;
  
      INSERT INTO Pet (Pet, PetName, IdPerson)
      SELECT Pet, PetName, ID
      FROM Person;
  
      INSERT INTO PersonPet (FK_Person, FK_Pet)
      SELECT ID, IdPerson
      FROM Pet;
  
      ALTER TABLE Pet DROP Column IdPerson;
  </code></pre>
  
  <ol start="2">
  <li>avoid temporarily modifying the Pet table</li>
  </ol>
  
  <pre><code>
      INSERT INTO Pet (Pet, PetName)
      SELECT Pet, PetName
      FROM Person;
  
      WITH
        CTE_Person
        AS
        (SELECT
          Id, Pet, PetName
          ,ROW_NUMBER() OVER (PARTITION BY Pet, PetName ORDER BY Id) AS row_number
        FROM Person
        )
        ,CTE_Pet
        AS
        (SELECT
          Id, Pet, PetName
          ,ROW_NUMBER() OVER (PARTITION BY Pet, PetName ORDER BY Id) AS row_number
        FROM Pet
        )
        ,CTE_Joined
        AS
        (SELECT
          CTE_Person.Id AS Person_Id,
          CTE_Pet.Id AS Pet_Id
        FROM
          CTE_Person
          INNER JOIN CTE_Pet ON
          CTE_Person.Pet = CTE_Pet.Pet
          CTE_Person.PetName = CTE_Pet.PetName
          AND CTE_Person.row_number = CTE_Pet.row_number
        )
        INSERT INTO PersonPet (FK_Person, FK_Pet)
        SELECT Person_Id, Pet_Id from CTE_Joined;
  </code></pre>
  
  <p><strong>Questions</strong></p>
  
  <ol>
  <li>Are both solutions correct? (I have tested the second solution and the result does seem to be correct, but I might have missed some corner case)</li>
  <li>What are the advantages/disadvantages of the two solution?</li>
  <li>Is there an easier way of doing the same data migration? (For my curiosity I would also be interested in answers that slightly modify my constraints (e.g. no duplicate entries in the Pet table), but please indicate which ones :) ).</li>
  </ol>