sql - 建模 1 :Many relationship with an attribute

Question

我遇到了一种 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 作为 primary_account_id 放入 Groups 表中，然后我相信我可以使用外键在primary_account_id、group_id 到 account_id、group_id。

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

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

在触发器之外的任何这些情况下，是否有任何方法可以在组内强制使用单个主帐户(因此主要是声明性 RI)？

谢谢!

Answer 1

关系基数

根据您的描述，您需要 1:N 关系，这意味着您不需要联结表Group_Accounts。只需从 Accounts 到 Groups 进行简单的 FK 即可。

特殊行

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

1. 使用 Accounts.is_primary 标志并通过过滤的唯一索引(如果您的 DBMS 支持)强制其唯一性(每个组)，
2. 或者您可以在群组中设置一个指向主帐户的 FK。但在后一种情况下，您必须小心选择实际上属于该组的主帐户。

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

Groups.FK1 表示:

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

上面 FK 中 group_id 的存在强制主帐户属于其主帐户所在的组。

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

<小时/>

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

选择第二个如果:

• 您的 DBMS 不支持过滤索引，
• 或者您的 DBMS 支持延迟约束，并且您需要始终强制主帐户存在(只需将 primary_account_no 设置为 NOT NULL)，
• 或者您实际上并不需要 account_id，因此您可能会少一个索引(取决于您的 DBMS 对 FK 索引的严格要求以及您的实际工作负载，您也许能够避免primary_account_no 上的索引，而不是 is_primary 上必须存在的索引)。