title: MySQL permutations of single list
body: <p>Bit of a database newbie here. I have a table list this:</p>
  
  <pre><code>author_id   book_id(PK)   book_title   other_book_info
  ======================================================
  1           1             Grass        etc.
  1           2             Fire         etc.
  2           3             Green        etc.
  2           4             Red          etc.
  </code></pre>
  
  <p>I'm interested in matching all books from Author1 with all books from Author2 with some correlation called book_similarity (outside the scope of the question). What's the best practice way of creating a table of all permutations like this?:</p>
  
  <pre><code>sim_id(PK)   book_id1    book_id2     book_similarity
  =====================================================
  1            1           3            High
  2            1           4            Low
  3            2           3            Low
  4            2           4            High
  </code></pre>
  
  <p>Book similarity is commutative (ie. matching <strong>1 vs. 3</strong> is the same as <strong>3 vs. 1</strong>) so I only need the permutations of book titles, not the combinations. </p>
  
  <p>However, I suspect that it would be best practice to create a longer table with all combinations that has 16 rows so that if I'm looking for a particular book (say #4), that I only need to look in book_id1 rather than book_id1 and book_id2. What's the consensus?</p>