CODEWITHSUNDEEP
oracle-databaseoracle9i
wundidajah
wundidajah

Reputation: 177

When should I use a nested table and when a reference?

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?

Upvotes: 0

Views: 421

Answers (1)

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

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...

Upvotes: 1

Related Questions