SQL 一对一关系和结果架构

Question

我正在创建一个抵押/贷款数据库 - 我有一个名为抵押的表，其字段为：

• mortgage_id
• client_id
• *rate_type* （可能是：fixed 或 tracker） ...等

根据rate_type 的选择，后面的字段会有所不同。例如，如果选择具有固定利率类型的抵押贷款，则用户将仅输入该固定利率。如果是跟踪抵押贷款，则需要跟踪利率（例如+0.90％）和基本利率（正在跟踪）例如0.50％[调整后利率= 1.40％]。

我的问题是：我该如何为此实现正确的表/字段结构。理想情况下，我不想拥有所有字段（突出显示）：

• mortgage_id
• client_id
• rates_type
• fixed_rate
• track_rate
• base_rate

因为它可能会导致混乱。有什么办法可以将它们分开到其他表中吗？也许另一个表的rate_details（一个用于固定，另一个用于跟踪）具有一对一的关系？

Answer 1

根据你的问题，我建议你可以创建3个表。

1 用于基本信息，一个用于存储固定费率详细信息的表，以及一个用于存储有关基本费率详细信息的表。

tblMortgages：

MortgageID |客户 ID |费率类型

tblFixedRates:

id |抵押贷款ID |固定速率

tblTrackerRates：

id |抵押贷款ID |跟踪率、基本率

Answer 2

您拥有的是一个具有类型（抵押）的 ER 模型，该模型提供一组通用属性，每个属性都是 0 或 1 个子类型的实例，这些子类型提供其他特定于子类型的属性。请参阅我的答案

派生概念 - 数据库设计注意事项

和

我应该吗设计一个 SQL Server 数据库以依赖 UNION 还是避免它？

了解如何解决这个问题。

Answer 3

将所有三列放在同一个表中是有效的，但根据需要仅使用其中的 1 或 2 列。所有三列都可以为 NULLABLE。

另一种替代方法是对任一类型的费率类型使用 2 列，但在处理固定费率时将其中一列设置为 0。由于您要添加两个费率来得出总跟踪费率，因此固定费率加上 0 的值就是固定费率。

[dbo].[theTable]
   [mortgage_id],
   [client_id],
   [rate_type],
   [base_rate],
   [rate]       // or whatever generic name is appropriate

因此，当 [rate_type] 被跟踪时，你会得到

[base_rate] = 0.50%
[rate] = 0.90%

total = 1.40%

，但当 [rate_type] 被固定时，你会得到

[base_rate] = 0%
[rate] = 0.70%

total = 0.70%

Answer 4

正如其他人所建议的那样，我的建议是固定利率抵押贷款和跟踪利率抵押贷款有 2 个子表，它们将 MortgageID 作为主键，并作为回到主抵押贷款表的外键。

这确保了一对一的关系，但不会强制抵押贷款只能存在于两个子表之一中。这是数据库不能很好执行的约束，我们在这里讨论的不是参照完整性。您可以在子表上使用触发器来确保只插入其他子表中不存在的抵押贷款，但恕我直言，触发器非常丑陋。我可能会坚持在应用程序层（即代码）而不是数据库中强制执行该不变式。

Answer 5

使用 SQL FOREIGN KEY 约束可以实现的最佳效果是确保每个抵押类型在每个子类型表中至多出现一次，即一对零或一关系，如果你愿意的话。强制实施此约束的一种方法是在 { ID , type } 上使用两列复合键，以便在整个架构中使用，并允许在子表中测试 type限制。下面是使用两种抵押子类型的粗略草图（大括号表示没有隐含顺序的列表）：

Mortgages { mortgage_ID , mortgage_type } 
   KEY { mortgage_ID } 
   KEY { mortgage_ID , mortgage_type }
   CONSTRAINT mortgage_type = 'Tracker'
              OR mortgage_type = 'Fixed'

FixedRateMortgages { mortgage_ID , mortgage_type , fixed_rate }
   KEY { mortgage_ID , mortgage_type }
   FOREIGN KEY { mortgage_ID , mortgage_type } REFERENCES Mortgages
   CONSTRAINT mortgage_type = 'Fixed';

FixedRateMortgages { mortgage_ID , mortgage_type , base_rate , track_rate }
   KEY { mortgage_ID , mortgage_type }
   FOREIGN KEY { mortgage_ID , mortgage_type } REFERENCES Mortgages
   CONSTRAINT mortgage_type = 'Tracker';

Clients { client_ID } 
   KEY { client_ID } ;

Agreements { mortgage_ID , mortgage_type , client_ID }
   KEY { mortgage_ID , mortgage_type , client_ID }
   FOREIGN KEY { mortgage_ID , mortgage_type } REFERENCES Mortgages
   FOREIGN KEY { client_ID } REFERENCES Client;

您没有指定 SQL 产品。在标准 SQL-92 中，可以使用声明为 DEFERRABLE INITIALLY DEFERRED 的 CREATE ASSERTION 约束来维护严格的一对一引用完整性，以封装这种“分布式”每个子类型表逻辑。然后，SQL 语句可以在事务中推迟 ASSERTION，修改引用和引用表，然后重新应用 ASSERTION（或通过提交事务自动执行此操作）。遗憾的是，现实生活中没有 SQL 产品支持 CREATE ASSERTION。根据供应商的不同，有一些解决方法，例如触发器、从行级 CHECK 约束调用的 SQL 函数中的表表达式、撤销表的写入权限，然后强制用户通过 CRUD 过程更新表，以确保也就是说

，在 SQL 中拥有一对零或一的关系通常是可以接受的，而且这样做确实可能有好处，例如使数据库约束更容易编写（从而减少错误），不强迫用户的灵活性使用一套程序等