什么是一对多关系的最佳设计，相互引用？ [英] What is best design for one-to-many relationship with back references to each other?

问题描述

我正在为一对多的关系找到SQL数据库模式的最佳设计。在我的项目中，我有对象，其中包含节点数量，我想要每个对象可以对 root_node 的可选外键引用。所以我的初始解决方案看起来像这样（为了清楚我跳过依赖问题）：

I am trying to find best design for SQL database schema for one-to-many relationship. In my project i have objects which consist of number of nodes and i would like each object to have optional foreign key reference to root_node. So my initial solution looks like this (for clarity i am skipping dependency problem):

-- schema A

CREATE TABLE objects (
   object_id integer NOT NULL PRIMARY KEY,
   root_node integer REFERENCES nodes(node_id),
    <some other data>
);

CREATE TABLE nodes (
   node_id integer NOT NULL PRIMARY KEY,
   object_id integer REFERENCES objects,
   <some other data>
);

然而，现在我们有两个表与外键引用，我不知道是一个好事情。所以我正在考虑另一种方法，而不是在对象表中将 root_node 分开存储为 root_nodes ：

However now we have two tables with foreign key references to each other which i am not sure is a good thing. So i am considering another approach when instead of putting root_node inside objects table it is stored separately as root_nodes:

-- schema B

CREATE TABLE objects (
   object_id integer NOT NULL PRIMARY KEY,
    <some other data>
);

CREATE TABLE root_nodes (
   object_id integer REFERENCES objects PRIMARY KEY,
   root_node integer REFERENCES nodes(node_id),
);

CREATE TABLE nodes (
   node_id integer NOT NULL PRIMARY KEY,
   object_id integer REFERENCES objects,
   <some other data>
);

所以我的问题是： A 和 B 设计认为是可接受的，或者有一个已知的最佳实践，其中一个优先于另一个？如果是这样，你能否提供理由，为什么一个模式更好？

So my question is: does both A and B designs consider to be acceptable or there is a known 'best practice' which will prefer one over the other? If so, could you please provide rationale why one of schema is better?

推荐答案

在模式B你可以有多个根节点对象和根节点可以是另一个对象的节点。模式A对象的最多一个根节点（这是我们想要的我猜的），但是共享第二个问题。我不知道是否有一些最佳实践，但这里有一些想法。

In schema B You can have multiple root nodes for the object and root node can be node of another object. Schema A forces at most one root node for the object (which is what we want I guess), but shares the second issue. I do not know if there is some "best practice" for this, but here are some ideas.

如果您需要更多的根节点用于该对象，那其实是非常简单的做，你只需要点标志：

If You need more root nodes for the object, it is actually very simple to do, You need just bit flag:

CREATE TABLE objects (
   object_id integer NOT NULL PRIMARY KEY,   
    <some other data>
);

CREATE TABLE nodes (
   node_id integer NOT NULL PRIMARY KEY,
   object_id integer REFERENCES objects,
   is_root bit NOT NULL
   <some other data>
);

如果您只需要一个根节点作为对象，您可以添加过滤的唯一索引：

If You want only one root node for the object, You can add filtered unique index:

CREATE UNIQUE NONCLUSTERED INDEX unique_root_for_object ON nodes
(
    object_id
)
WHERE (is_root = 1)

现在让它调用模式C。现在让我们返回到架构A并修复不同对象的根问题。您可以添加复合外键，以强制根节点为对象节点之一：

Lets call it schema C for now. Now lets return to the schema A and fix the "root from different object" issue. You can add composite foreign key to force root node be one of the object nodes:

ALTER TABLE objects WITH CHECK CHECK 
CONSTRAINT FK_objects_nodes FOREIGN KEY(object_id, root_node) 
REFERENCES nodes (object_id, node_id)

您需要在表节点上的（object_id，node_id）上的唯一索引才能使其工作。您当然还可以拥有没有根节点的对象，它们不会违反这个外键。

You would need unique index on (object_id, node_id) on table nodes for this to work. You can still have objects without root node of course, they would not violate this foreign key.

更好的模式A或C？模式C似乎更灵活，您可以在一个插入中添加节点作为根节点。您也可以轻松地将其切换到多根节点场景。另一方面，架构A允许您使用根节点信息创建对象的索引。当记录更改时，根节点的更改将被记录为对象的更改，而不会更改节点。依赖性更加明确，这将简化一些查询，ORM也希望它。

Is better schema A or C? Schema C seems more flexible, You can add node as root node in one insert for example. You can also easily switch it to "multiple root nodes" scenario. Schema A on the other hand allows You to create index on objects with root node information. When logging changes, change of root node would be logged as change of object, not change of the node. The dependency is more explicit, which would simplify some queries a bit and ORM would like it too.

可能还有其他方法可以做到这一点。根据经验，我会尝试坚持不允许设计数据不一致的模式。

There may be another ways to do this. As a rule of thumb I would try to stick to schemas that do not allow data inconsistency by design.

这篇关于什么是一对多关系的最佳设计，相互引用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！