SQL 多表之间的SQL映射

SQL mapping between multiple tables

提问by Justin R.

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

这是一个 SQL 设计问题。首先，设置。我有三张表：

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;

1. A，根据对链接服务器的查询自动填充。此表中的数据不可更改；
2. B，它只有十几行，包含 As 集合的名称；
3. AtoB，这是将As组织成命名集合的映射表，两列都有外键；

For example, A contains:

例如，A 包含：

1. Giraffe
2. Owl
3. Tiger

1. 长颈鹿
2. 猫头鹰
3. 老虎

And B contains:

B 包含：

1. Seattle Zoo
2. San Jose Zoo

1. 西雅图动物园
2. 圣何塞动物园

And AtoB contains:

AtoB 包含：

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

1,1（西雅图的长颈鹿）
2,1（西雅图的猫头鹰）
3,1（西雅图的老虎）
2,2（圣何塞的猫头鹰）

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:

我被要求在其中一些集合中包含 A 中未找到的项目。因此，我创建了一个表 C，该表具有与 A 相同的标识和名称列，并填充它。与前面的示例保持一致，假设 C 包含：

1. Dragon

1. 龙

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

问题是，如何在 AtoB 中包含来自 C 的项目？如果我需要在西雅图动物园里放一条龙怎么办？

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.

我的第一直觉是创建一个包含 A 和 C 联合的视图 V，并将 AtoB 修改为 VtoB。这就是我的天真得到回报的地方：无法为视图创建外键。

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

我怀疑有一种标准的、正确的方法可以将一个或多个 A 或 C 与 B 相关联。

回答by Kevin Peno

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

要扩展 Arthur Thomas 的解决方案，这里有一个union在子选择中没有 WHERE 的方法，以便您可以创建通用视图：

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"

回答by Arthur Thomas

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:

如果您不能在 A 中放置龙，那么您将需要创建另一个表和另一个链接表。问题是创建一组需要存储的唯一数据（另一个表），该数据集不能与 A 相同。由于它不是相同的集合，因此您不能再使用具有外部数据的链接表 (AtoB)确保链接是来自集合 A 的引用的键。因此您可以创建如下表：

imaginary_creatures

imaginary_creatures

• id
• name

• ID
• 姓名

imaginary_creatures_to_b

imaginary_creatures_to_b

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

• imaginary_creatures_id（链接到 imaginary_creatures 表）
• b_id（链接到动物园表）

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

以后当你想把所有的动物都放在动物园里时，你可以做一个 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.

可能有更好的写法，但它应该适合您的目的。

回答by HLGEM

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.

Arthur Thomas 有一个很好的解决方案，另一种可能的解决方案是在链接表中添加一列，指示它与哪个表（A 或 C）相关。然后通过触发器而不是外键强制建立关系。但实际上 Arthur 的解决方案是做这类事情的首选方式。

回答by rosscj2533

What you want to do is put Dragon in A, and if you want to select ALL records from A regardless of if they have a matching record in AtoB, do a LEFT OUTER JOIN. Something like this:

您要做的是将 Dragon 放入 A 中，如果您想从 A 中选择所有记录，而不管它们在 AtoB 中是否有匹配的记录，请执行 LEFT OUTER JOIN。像这样的东西：

SELECT * FROM A
LEFT OUTER JOIN AtoB
ON A.id = AtoB.A_ID

Edit: This would only work if you could add your new records to A. I missed the fact that you are not able to. I think Arthur Thomas's solution is what you want.

编辑：这只有在您可以将新记录添加到 A 时才有效。我错过了您无法添加的事实。我认为 Arthur Thomas 的解决方案正是您想要的。