title: Refactoring a One-to-many relation to a Many-to-Many in MySQL: How to formulate the query?
body: <p>In the initial 'version' of the application that I'm working on, a design consideration wasn't taken into account - no one thought of it.</p>
  
  <p>However, it seems that the original one-to-many relation needs to be refactored into a many-to-many. My question is how best to do this? I'm using MySQL for persistence. </p>
  
  <p>Populating the relationship table will only be a one time effort, I'd rather go with a simple query or a stored procedure approach (I'm not well versed with the latter); rather than write  java/jdbc based logic to do it (I know I can and it's not too difficult, but that's <em>not</em> what I want)</p>
  
  <p>So here's an example of the relation:</p>
  
  <pre><code>|VirtualWhiteBoard| -1------*- |Post|
  </code></pre>
  
  <p>A virtual white board can have many posts. The new functionality is: 1 post should belong to multiple white boards if the user chooses to 'duplicate' current white board (not thought of before)</p>
  
  <p>The schema looks like this:</p>
  
  <pre><code>VirtualWhiteBoard (wallName, projectName,dateOfCreation,..., Primary_Key(wallName, projectName));
  Post(post_id, wallName,postData,..., Primary_Key(post_id), Foreign_Key(wallName, projectName));
  </code></pre>
  
  <p>The virtual white board has a composite primary key (wallName, projectName) and each post has a post_id as primary key</p>
  
  <p>Question: Take the primary keys from VirtualWhiteBoard and Post and add it to the new relation 'has_posts':</p>
  
  <pre><code>|VirtualWhiteBoard| -1------*- |has_Post| -*------1- |Post|
  </code></pre>
  
  <p>To keep the previous relationships intact and then drop the foreign key column of wallName in Post.</p>
  
  <p>How best to achieve this? Would a query suffice or stored procedures would be required?</p>
  
  <p>(Although I can do this in the 'application' I'd prefer to do it this way, since such refactorings are bound to arise and I don't want unnecessary java-code lying around that'll need to be maintained and would personally prefer to have such a skill too :)</p>