sql - 建模 1 :Many relationship with an attribute

我遇到了一个让我立刻感到奇怪的 table 设计，但现在我仔细考虑了它，我似乎无法想出一个让我真正满意的设计。

现有的设计(简化)是:

CREATE TABLE Accounts (
    account_id      INT NOT NULL,
    account_name    VARCHAR(50) NOT NULL,
    CONSTRAINT PK_Accounts PRIMARY KEY CLUSTERED (account_id)
)

CREATE TABLE Groups (
    group_id      INT NOT NULL,
    group_name    VARCHAR(50) NOT NULL,
    CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (group_id)
)

CREATE TABLE Group_Accounts (
    group_id      INT NOT NULL,
    account_id    INT NOT NULL,
    is_primary    BIT NOT NULL,
    CONSTRAINT PK_Group_Accounts PRIMARY KEY CLUSTERED (group_id, account_id)
)

虽然它看起来像标准的多对多关系，但一个帐户实际上永远不会属于一个以上的组。我立即想，“好吧，我们可以将 group_id 放入 Accounts 表中，这应该可以工作。”但是我会用 is_primary 属性做什么呢？

我可以将 account_id 作为 Groups 放入 primary_account_id 表中，然后我相信我可以使用 primary_account_id, group_id to account_id, group_id 上的外键强制执行 RI。

或者，我可以将“is_primary”标志移动到 Accounts 表中。也许这是最好的解决方案？

对每种方法的优缺点有什么想法吗？我是否遗漏了任何潜在问题？还有其他一些我错过的选择吗？

有没有办法在触发器之外的任何这些情况下强制执行组内的单个主帐户(因此主要是声明性 RI)？

谢谢!

最佳答案

关系基数

根据您的描述，您需要 1:N 关系，这意味着您不需要连接表 Group_Accounts 。只需从 Accounts 到 Groups 的简单 FK 就可以了。

特殊行

下一个问题是如何在 N 侧( Accounts )选择一行作为“特殊”。您可以:

使用 Accounts.is_primary 标志并通过过滤的唯一索引(如果您的 DBMS 支持)强制其唯一性(每个组)，

或者您可以在 Groups 中使用 FK 指向主帐户。但是，在后一种情况下，您必须小心选择实际属于该组的主帐户。

第二种方法可以类似于这样建模:


Groups.FK1 表示:

FOREIGN KEY (group_id, primary_account_no) REFERENCES Accounts (group_id, account_no)

group_id 在上面的 FK 中的存在是强制主帐户属于它是主帐户的组的原因。

创建新帐户时，请注意生成 account_no 的方式。您需要执行 something like this 以避免并发环境中的竞争条件(当然，实际代码会因 DBMS 而异)。

如果您的 DBMS 支持过滤索引，则选择第一种方法，并且没有特定理由选择第二种方法。

在以下情况下选择第二个:

你的 DBMS 不支持过滤索引，

或您的 DBMS 支持延迟约束，您需要始终强制执行主帐户的存在(只需使 primary_account_no NOT NULL)，

或者您实际上不需要 account_id ，因此您可能会少一个索引(取决于您的 DBMS 对 FK 索引的严格程度以及您的实际工作量，您可以避免在 primary_account_no 上使用索引，而不是使用该索引必须存在于 is_primary 上)。

关于sql - 建模 1 :Many relationship with an attribute，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34665144/