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;

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 1:

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"

Solution 2:

If you can't put a Dragon in A then you will need to create another table and another link table. The problem is creating a unique set of data that needs to be stored (another table) that cannot be the same set as A. Since it isn't the same set then you can no longer use the link table (AtoB) which has foreign keys that ensure that the link is a reference from set A. So you could create a tables like this:

imaginary_creatures

• id
• name

imaginary_creatures_to_b

• imaginary_creatures_id (link to imaginary_creatures table)
• b_id (link to zoos table)

Later when you want to get all creatures in a zoo you can do a UNION

SELECT A.Name FROM A where A.ID IN 
   (SELECT AB.A_ID FROM AtoB AB WHERE B_ID = 
      (SELECT B.ID FROM B WHERE B.Name = 'Zoo Name'))
UNION
SELECT i.name FROM imaginary_creatures i i.id IN 
   (SELECT ic.imaginary_creatures_id FROM imaginary_creatures_to_c ic 
    WHERE ic.b_id = (SELECT B.ID FROM B WHERE B.Name = 'Zoo Name'))

There may be a better way of writing that, but it should work for your purposes.

Solution 3:

Arthur Thomas has a good solution, the other possible solution is to add a column to the link table indicating which table (A or C) it is related to. Then enforce the relationships through triggers rather than foreign keys. But really Arthur's solution is the preferred way of doing this sort of thing.