Skip Headers

# Schema Case 1: Aggregation of Two Tables into One

Previous

Next

A common schema optimization technique is to aggregate two tables into a single table. This improves read and write performance by requiring only one database operation instead of two.

Table: Original Schema (Aggregation of Two Tables Case) and Table: Optimized Schema (Aggregation of Two Tables Case) illustrate the table aggregation technique.

Original Schema (Aggregation of Two Tables Case)

Elements | Details
--- | ---
Title | ACME Member Location Tracking System
Classes | Member, Address
Tables | MEMBER, ADDRESS
Relationships | Source, Instance Variable, Mapping, Target, Member, address, one-to-one, Address

The nature of this application dictates that developers always look up employees and addresses together. As a result, querying a member based on address information requires a database join, and reading a member and its address requires two read statements. Writing a member requires two write statements. This adds unnecessary complexity to the system, and results in poor performance.

A better solution is to combine the MEMBER and ADDRESS tables into a single table, and change the one-to-one relationship to an aggregate relationship. This lets you read all information with a single operation, and doubles the update and insert speed, because only a single row in one table requires modifications.

Optimized Schema (Aggregation of Two Tables Case)

Elements | Details
--- | ---
Classes | Member, Address
Tables | MEMBER
Relationships | Source, Instance Variable, Mapping, Target, Member, address, aggregate, Address

See Also

  Schema Optimization