How should I decide whether to use a nested table or a reference?
我该如何决定是使用嵌套表还是引用?
For example:
We have an airline and a flights table:
我们有航空公司和航班表:
CREATE TABLE airline OF airline_ty(
token VARCHAR2(8),
description VARCHAR2(20)
)
CREATE TABLE flights OF flights_ty(
flightNumber NUMBER(10)
securityLevel VARCHAR2(10)
)
Should I know make a reference in airline (flights REF flights_ty) or go for a nested table?
我应该知道在航空公司(航班REF flights_ty)中提供参考还是去寻找嵌套表?
1 个解决方案
#1
It depends on the requirements for usage of the data. In your example with airlines and flights a flight should have a foreign key to its airline. The main table is flights and airlines is a codebook.
这取决于数据使用的要求。在您的航空公司和航班的例子中,航班应该有航空公司的外键。主要表是航班,航空公司是码本。
An example case where a nested table would be a good choice:
A customer in a core banking application has several phone numbers, email addresses etc. You need to hold this data for a customer, but you do not evaluate it (all customers with this email etc.), you just display it together with other customer detail. You cannot have an extra table for each one to many property, because you have much more interesting data, like accounts, loans, credit cards, account statements, behavior score cards etc.
嵌套表是一个不错的选择的示例:核心银行应用程序中的客户有多个电话号码,电子邮件地址等。您需要为客户保留此数据,但您不评估它(所有客户都有此电子邮件等),您只需将其与其他客户详细信息一起显示即可。你不能为每一对多的财产增加一张桌子,因为你有更多有趣的数据,比如账户,贷款,信用卡,账户报表,行为记分卡等。
You have always take into account, what will be the redundancy, reuse, importance, property vs. entity, aggregation vs. composition...
你总是考虑到,冗余,重用,重要性,财产与实体,聚合与组合将是什么......
#1
It depends on the requirements for usage of the data. In your example with airlines and flights a flight should have a foreign key to its airline. The main table is flights and airlines is a codebook.
这取决于数据使用的要求。在您的航空公司和航班的例子中,航班应该有航空公司的外键。主要表是航班,航空公司是码本。
An example case where a nested table would be a good choice:
A customer in a core banking application has several phone numbers, email addresses etc. You need to hold this data for a customer, but you do not evaluate it (all customers with this email etc.), you just display it together with other customer detail. You cannot have an extra table for each one to many property, because you have much more interesting data, like accounts, loans, credit cards, account statements, behavior score cards etc.
嵌套表是一个不错的选择的示例:核心银行应用程序中的客户有多个电话号码,电子邮件地址等。您需要为客户保留此数据,但您不评估它(所有客户都有此电子邮件等),您只需将其与其他客户详细信息一起显示即可。你不能为每一对多的财产增加一张桌子,因为你有更多有趣的数据,比如账户,贷款,信用卡,账户报表,行为记分卡等。
You have always take into account, what will be the redundancy, reuse, importance, property vs. entity, aggregation vs. composition...
你总是考虑到,冗余,重用,重要性,财产与实体,聚合与组合将是什么......