Optimization Strategy: Denormalization

Why denormalize?

• The result of normalization is a design that is structurally consistent with minimal redundancy.
• However, a normalized database does not always provide maximum processing efficiency — many queries need several joins.
• It may be necessary to accept the loss of some benefits of a fully normalized design in favour of performance.

Also consider that denormalization:

• makes implementation more complex;
• often sacrifices flexibility;
• may speed up retrievals but slows down updates.

What is denormalization?

The term is also used more loosely for combining two relations into one new relation that is still normalized but contains more nulls than the originals.

When to apply denormalization

Consider it specifically to speed up frequent or critical transactions, in these situations:

1. Combining 1:1 relationships
2. Duplicating non-key attributes in 1:* relationships to reduce joins
3. Duplicating foreign key attributes in 1:* relationships to reduce joins
4. Introducing repeating groups
5. Partitioning relations

Sample relations (DreamHome)

The relation diagram links: Client —Attends— Interview; Client —Requests— Viewing; PropertyForRent —Takes— Viewing; PrivateOwner —POwns— PropertyForRent; Branch —Offers— PropertyForRent; Branch —Provides— Telephone.

1. Combining 1:1 relationships

Client and Interview have a 1:1 relationship (a client attends at most one interview). If they are frequently accessed together, combine them into one relation ClientInterview.

Combined:

No join needed any more — but clients with no interview now have nulls in the interview columns. This is the “loose” form of denormalization (still normalized, more nulls).

2. Duplicating non-key attributes in 1:* relationships

A frequent query lists properties with their owner’s last name:

SELECT p.*, o.lName
FROM   PropertyForRent p, PrivateOwner o
WHERE  p.ownerNo = o.ownerNo AND branchNo = 'B003';

If we duplicate lName from PrivateOwner into PropertyForRent, the join disappears:

SELECT p.*
FROM   PropertyForRent p
WHERE  branchNo = 'B003';

Special case: lookup tables

Lookup (reference/code) tables hold a code and its description, e.g. PropertyType(type, description) with H = House, F = Flat, and PropertyForRent stores only the code:

If the lookup table is used frequently or critically, and the description rarely changes, duplicate the description in the child relation so no join is needed:

Figure 18.5 — Modified PropertyForRent with duplicated description. The original lookup table can be kept for validation.

3. Duplicating foreign key attributes in 1:* relationships

Original: PrivateOwner —POwns→ PropertyForRent ←Offers— Branch. A frequent query: list all private owners at a branch:

SELECT o.lName
FROM   PropertyForRent p, PrivateOwner o
WHERE  p.ownerNo = o.ownerNo AND branchNo = 'B003';

Remove the join by duplicating the foreign key branchNo into PrivateOwner — i.e. introducing a direct relationship Branch —Serves→ PrivateOwner:

SELECT o.lName
FROM   PrivateOwner o
WHERE  branchNo = 'B003';

SELECT o.lName
FROM   Staff s, PropertyForRent p, PrivateOwner o
WHERE  s.staffNo = p.staffNo AND p.ownerNo = o.ownerNo
  AND  s.branchNo = 'B003';

4. Introducing repeating groups

Repeating groups are removed during normalization (1NF) into a separate relation — e.g. a branch’s telephone numbers are held in Telephone(telNo, branchNo). If access to the phone numbers is critical, fold them back into Branch as separate columns:

telNo1 can be marked as an alternate key (AK). Consider this only when:

• the absolute number of items in the repeating group is known (here, a maximum of three);
• the number is static and will not change over time;
• the number is not very large — typically not more than 10 (less important than the first two).

5. Partitioning relations

Rather than combining relations, decompose them into smaller, more manageable partitions. Two main types:

Advantages and disadvantages

Table 18.1 — Advantages and disadvantages of denormalization.

Quick review