主页 / dba / 问题 / 158577
Accepted
Luca
Asked: 2016-12-20 06:57:40 +0800 CST

两个实体之间的两条路径

  • 772

在我的数据库中，实体是：

  • designed_object：通用设计对象

  • designed_object_param：与设计对象相关的参数，有类型（param_type）、默认值（param_value）、最大值和最小值（param_max/min_value）

  • real_object：设计对象的一个​​实例

  • real_object_param：实例的实际参数（param_value）

它们的关系如下（外键）：

  • designed_object - real_object: 1..n
  • designed_object - designed_object_param: 1..n
  • 真实对象 - 真实对象参数：1..n
  • designed_object_param - real_object_param: 1..1

问题是从 real_object_param 到 designed_object 有两条路径：

  • real_object_param -> real_object -> designed_object
  • real_object_param -> designed_object_param -> designed_object

使用此设计，如果由于某些错误，real_object_param 引用了错误的 designed_object_param，则两条路径可能会从相同的 real_object_param 指向两个不同的行。我知道这可以通过触发器来避免。有没有更好的设计？

谢谢。

实体关系

database-design database-diagrams

2 个回答

  1. 我在这里重命名了一些东西，只是为了让我更容易思考。其中大部分是不言自明的。本质上，问题和解决方案与上一个问题非常相似，答案更详细。


    在此处输入图像描述

    • 1
  2. Best Answer

    在您的实际架构中，您可以添加一个designed_object_param分配给不同的designed_object.

    如果您想要 1 - 1 约束，请向id_designed_object中添加一个字段real_object_param，并将 FK 编写为(id_designed_object, id_designed_object_param)。这将确保只能将设计对象的参数添加到real_object_param.

    您还可以添加id_designed_object到 FK betweenreal_object_param和real_objectas (id_object, id_designed_object)。我认为这涵盖了所有可能性。

    最终架构应类似于此：

    create temp table designed_object
(
    id_designed_object int not null PRIMARY KEY
);

create temp table designed_object_param
(
    id_designed_object int4 not null REFERENCES designed_object (id_designed_object),
    id_designed_object_param int not null,
    PRIMARY KEY (id_designed_object, id_designed_object_param)
);

create temp table real_object
(
    id_real_object int4 not null,
    id_designed_object int4 not null REFERENCES designed_object (id_designed_object),
    PRIMARY KEY (id_real_object, id_designed_object)
);

create temp table real_object_param
(
    id_real_object int4 not null,
    id_real_object_param int4 not null,
    id_designed_object int4 not null,
    id_designed_object_param int4 not null,
    PRIMARY KEY (id_real_object, id_designed_object, id_real_object_param),
    FOREIGN KEY (id_real_object, id_designed_object) REFERENCES real_object (id_real_object, id_designed_object),
    FOREIGN KEY (id_designed_object, id_designed_object_param) REFERENCES designed_object_param (id_designed_object, id_designed_object_param)
);

    可以在这里查看：http ://rextester.com/GKU8783

    • 0

相关问题