SQL mapping between multiple tables

database-design, foreign-key-relationship, sql, union

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"

Problem

This is a SQL design question. First, the setup. I have three tables: - A, which is automatically populated based on a query against a linked server. The data in this table cannot be changed; - B, which has just a dozen or so rows, containing the names for collections of As; - AtoB, which is the mapping table by which As are organized into named collections, with foreign keys on both columns; For example, A contains: - Giraffe - Owl - Tiger And B contains: - Seattle Zoo - 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: - 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.

Original source