主页 / dba / 问题 / 193292
Accepted
Arthur Tarasov
Asked: 2017-12-17 00:27:03 +0800 CST

构建尽可能多的外键是一种好习惯吗？

  • 772

例如，有三个表：players、dungeons和instances。

一个player有很多dungeons，一个dungeon有很多instances，就像一个著名的魔兽世界游戏。每个player都有一个dungeon_id作为外键，每个dungeon都有一个instance_id作为外键。那将是一种足够的极简主义方法。player每一个都instance可以通过dungeon表访问。这将减少 1 列，但查询时间更长。

另一种方法是作为外键添加player_id到表中。instances现在玩家可以直接从instances表中查询。更短的查询，但外键多一列。

从长远来看，哪种方法更好？在实践中，似乎在逻辑上可能的任何地方都添加外键会更容易。但是这不会导致更紧密的耦合并使代码更难重构吗？

foreign-key

2 个回答

  1. 另一种方法是将 player_id 作为外键添加到实例表中。现在玩家可以直接从实例表中查询。更短的查询，但外键多一列。

    IMO，这将是不好的方法。这是一种循环引用。

    由于您有一对多关系，因此映射表将是分开的，地牢、实例和玩家每个都将具有定义其一对多关系的主表和映射表。

        CREATE TABLE instances (
    instance_id INT PRIMARY KEY
    ,instance VARCHAR(100) NOT NULL
    )

CREATE TABLE dungeons (
    dungeon_id INT PRIMARY KEY
    ,dungeon VARCHAR(100) NOT NULL
    )

CREATE TABLE player (
    playerid INT PRIMARY KEY
    ,player VARCHAR(100) NOT NULL
    )


CREATE TABLE dungeon_instance (
    dungeon_instance_id INT identity(1, 1)
    ,dungeon_id INT NOT NULL
    ,instance_id INT NOT NULL CONSTRAINT FK_instances FOREIGN KEY (instance_id) REFERENCES instances(instance_id)
    ,CONSTRAINT FK_dungeon FOREIGN KEY (dungeon_id) REFERENCES dungeons(dungeon_id)
    ,PRIMARY KEY (
        dungeon_id
        ,instance_id
        )
    )

CREATE TABLE player_dungeon (
    player_dungeon_id INT NOT NULL
    ,playerid INT NOT NULL
    ,dungeon_id INT NOT NULL
    ,CONSTRAINT FK_player FOREIGN KEY (playerid) REFERENCES player(playerid)
    ,CONSTRAINT FK_dungeon FOREIGN KEY (dungeon_id) REFERENCES dungeons(dungeon_id)
    ,PRIMARY KEY (
        playerid
        ,dungeon_id
        )
    )

    如果您在播放器和实例之间有非常频繁的查询，并且输出中不需要地牢表并且数据量很高，那么您可以进行非规范化。

    是否创建player_instance mapping table取决于上述要求。

    但希望通过以下方式实现非规范化index view

    • 1
  2. Best Answer

    理想情况下，实体不应该有外键，应该被认为是主人。所以player，dungeon并且instance是拥有与他们自己的特征相关的信息/属性/字段的大师。您可以为Enitity-Relations提供一个单独的表。因此，您将拥有player_dungeonand dungeon_instance（根据 PS，两者都是一对多）。在这里，如果你想知道instances属于一个player，你需要加入这两个表。

    现在出现了著名的辩论：规范化与非规范化。第一种方法是书本上的，并且会奏效。但是随着表的增长很难扩展（尽管有适当的索引）。因此，您尝试根据这些表上发生的查询量对上述解决方案进行非规范化。

    player每一个都instance可以通过dungeon表访问。

    由于您在此处指定了一个查询，我假设这是一个频繁查询（瓶颈）。如果有很多这样的查询，你可以有另一个关系表player_instance。现在我们已经解决了延迟的问题，但产生了一个更大的问题：

    一致性：现在有两种方法可以找到instances属于 a 的player。一种是直接按player_instance表，另一种是通过player_dungeon和连接dungeon_instance。我们必须始终从这两种方法中得到相同的答案。为了确保我们现在需要让我们的写入受到影响。如何？

    • 在添加每个玩家-地牢映射时，除了进入我们的 player_dungeon表之外，我们还需要插入到player_instance 表中（我们必须查询dungeon_instance以查看映射到该特定地牢的所有实例）。
    • 在添加新dungeon_instance玩家时，我们需要将所有现有玩家映射到我们映射到特定地牢的实例。听起来不太好，是吗？

    我们还没有完成。所有这些操作都应该是原子的（即在事务中），否则会在短时间内出现不一致。

    最后，我们需要做出取舍。您想让您的数据库读取速度变慢还是写入受到影响？您是否需要使您的系统响应/可用或一致？决定权在你。

    详情请阅读CAP 定理。

    • 1

相关问题