title: SQL mapping between multiple tables
body: <p>This is a SQL design question. First, the setup. I have three tables:</p>
  
  <ol>
  <li>A, which is automatically populated based on a query against a linked server. The data in this table cannot be changed;</li>
  <li>B, which has just a dozen or so rows, containing the names for collections of As;</li>
  <li>AtoB, which is the mapping table by which As are organized into named collections, with foreign keys on both columns;</li>
  </ol>
  
  <p><img src="https://i.stack.imgur.com/XQ46g.png" alt="SQL Mapping Table"></p>
  
  <p>For example, A contains:</p>
  
  <ol>
  <li>Giraffe </li>
  <li>Owl</li>
  <li>Tiger</li>
  </ol>
  
  <p>And B contains:</p>
  
  <ol>
  <li>Seattle Zoo </li>
  <li>San Jose Zoo</li>
  </ol>
  
  <p>And AtoB contains:</p>
  
  <p>1,1 (Giraffe in Seattle)<br>
   2,1 (Owl in Seattle)<br>
   3,1 (Tiger in Seattle)<br>
   2,2 (Owl in San Jose)  </p>
  
  <p>Now, the problem: </p>
  
  <p>I've been asked to include in some of these collections items not found in A. So, I create a table, C, with the same identity and Name columns as A, and populate it. In keeping with the earlier example, let's say C contains:</p>
  
  <ol>
  <li>Dragon</li>
  </ol>
  
  <p>The question is, how do I include items from C in AtoB? What if I need to include a Dragon in the Seattle Zoo? </p>
  
  <p>My first instinct, being naive, was to create a view V containing the union of A and C, and modifying AtoB to be VtoB. That's where my naivety paid off: one cannot create a foreign key to a view. </p>
  
  <p>I suspect that there's a standard, correct means of relating one or more A OR C with a B. </p>