Search code examples
sqldatabase-designunionforeign-key-relationship

SQL mapping between multiple tables

This is a SQL design question. First, the setup. I have three tables:

  1. A, which is automatically populated based on a query against a linked server. The data in this table cannot be changed;
  2. B, which has just a dozen or so rows, containing the names for collections of As;
  3. AtoB, which is the mapping table by which As are organized into named collections, with foreign keys on both columns;

SQL Mapping Table

For example, A contains:

  1. Giraffe
  2. Owl
  3. Tiger

And B contains:

  1. Seattle Zoo
  2. San Jose Zoo

And AtoB contains:

1,1 (Giraffe in Seattle)
2,1 (Owl in Seattle)
3,1 (Tiger in Seattle)
2,2 (Owl in San Jose)

Now, the problem:

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:

  1. Dragon

The question is, how do I include items from C in AtoB? What if I need to include a Dragon in the Seattle Zoo?

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.

I suspect that there's a standard, correct means of relating one or more A OR C with a B.

Solution

  • To expand on Arthur Thomas's solution here's a union without the WHERE in the subselects so that you can create a universal view:

    SELECT A.Name as Animal, B.Name as Zoo FROM A, AtoB, B
    WHERE AtoB.A_ID = A.ID && B.ID = AtoB.B_ID 
UNION
SELECT C.Name as Animal, B.Name as Zoo FROM C, CtoB, B
    WHERE CtoB.C_ID = C.ID && B.ID = CtoB.B_ID

    Then, you can perform a query like:

    SELECT Animal FROM zoo_animals WHERE Zoo="Seattle Zoo"