mysql - 复制具有重要关系的关系数据

Question

假设我们有这些表:

                  +-------------------------+
                  | Orphanage               |
                  |-------------------------|
                  | ID                      |
                  |                         |
                  +-------------------------+


      +-------------------------+    +-------------------------+
      | Room                    |    | Orphan                  |
      |-------------------------|    |-------------------------|
      | ID                      |    | ID                      |
      | Orphanage ID            |    | Orphanage ID            |
      |                         |    |                         |
      +-------------------------+    +-------------------------+


                     +-------------------------+
                     | Room Movement           |
                     |-------------------------|
                     | ID                      |
                     | Orphan ID               |
                     | From Room ID            |
                     | To Room ID              |
                     |                         |
                     +-------------------------+

Room Movement 当孤儿从一个房间移动到另一个房间时获得一个新条目。

现在假设我想复制整个 Orphanage，包括所有相关实体。 Room 和 Orphan 很容易复制 - 保存新的 Orphanage 以创建 ID，并将其传递给 Room 和 Orphan 的克隆 - 但是 Room 中的条目运动稍微复杂一些。我将不得不为 Orphans 和 Rooms 维护一个从旧 ID 到新 ID 的映射，并对每个插入进行查找。这在这个人为的示例中很简单，但实际上我们在 Orphanage 下有很多此类关系，以及这些 FK 关系的嵌套级别。

两个问题:这是 ORM 应该处理的事情吗？这不是我们的应用程序独有的问题，而且似乎通过正确配置的外键和某种“克隆策略”系统，ORM 应该完成繁重的工作并按正确的顺序做事。

如果没有，是否可以在某个地方阅读此问题并了解其他人如何在不创建大量代码的情况下解决它？

Answer 1

看起来你没能理解两者之间的区别代理键和自然键。您的 ID 列是代理键，它们出现在您的模型中的原因是因为它们确定ORM 和 RDBMS 的任务更容易。你的模型应该是即使您省略它们也是一致的。

显然，您的模型不是因为如果您这样做，那么 Orphanage 就没有属性，Room 和 Orphan 仅引用它们所在的孤儿院位于等等。

要解决该问题，您需要将自然键 添加到您的模型中。一个自然键 (NK) 是一个或多个列的组合表中独树一帜。对于 Orphanage，我会添加一列 name 和NK 将是孤儿院的名字(这是一个合理的没有两个孤儿院同名的简化，但在假设不成立的现实世界):

Orphanage
---------
name
NK: (name)

对于 Room，我们可以添加一列 room_nr 并将其作为 NK。但这还不够，因为显然两个孤儿院可以有一个房间相同的 room_nr，所以 orphanage_id 和room_nr 应该是 NK:

Room
----
orphanage_id
room_nr
NK: (orphanage_id, room_nr)

用同样的推理来弄清楚Kid 的 NK 是什么(我喜欢这样表名更好，因为“孤儿”和“孤儿院”这两个词太相似的)。我们假设每个孤儿院的名称都是唯一的。所以那里同一个孤儿院里不能有两个“乔”:

Kid
---
orphanage_id
name
NK: (orphanage_id, name)

对于 RoomMovement:

RoomMovement
------------
kid_id
from_room_Id
to_room_id
NK: (kid_id, from_room_id, to_room_id)

请注意，此表有点不正确。你不能代表一个 child 从房间 A 移动到 B 到 A，然后再回到 B。这样的当你不花时间思考时，设计错误总是会发生关于模型的自然键。但是对于克隆的问题孤儿院，这无关紧要。

定义了所有自然键后，下一步就变得非常容易了。说旧孤儿院名为“St. James”，您想将其克隆到“圣克拉拉”:

-- make a new orphanage
insert into Orphanage values ('St. Clara');

-- copy all the rooms
insert into Room(room_nr, orphanage_id)
select
    r.room_nr,
    (select id from Orphanage where name = 'St. Clara')
from
    Room r
    join Orphanage o on r.orphanage_id = o.id
where
    o.name = 'St. James'

-- copy all the kids in almost the same way.
insert into Kid(name, orphanage_id)
select
    k.name,
    (select id from Orphanage where name = 'St. Clara')
from
    Kid k
    join Orphanage o on k.orphanage_id = o.id
where
    o.name = 'St. James'

-- copy all the room movements
insert into RoomMovement (from_room_id, to_room_id, kid_id)
select
    (select id from Room
     where room_nr = r_from.room_nr and orphanage_id = o_to.id),
    (select id from Room
     where room_nr = r_to.room_nr and orphanage_id = o_to.id),
    (select id from Kid
     where name = k.name and orphanage_id = o_to.id)
from
    RoomMovement rm
    join Room r_from on rm.from_room_id = r_from.id
    join Room r_to on rm.to_room_id = r_to.id
    join Kid k on rm.kid_id = k.id
    join Orphanage o_from on o_from.id = k.orphanage_id
    join Orphanage o_to on o_to.name = 'St. Clara'
where
    o_from.name = 'St. James';