Skip Headers

# Schema Case 3: Collapsed Hierarchy

Previous

Next

A common mistake when you transform an object-oriented design into a relational model, is to build a large hierarchy of tables on the database. This makes querying difficult, because queries against this type of design can require a large number of joins. It is usually a good idea to collapse some of the levels in your inheritance hierarchy into a single table.

Table: Original Schema (Collapsed Hierarchy Case) represents a system that assigns clients to a company's sales representatives. The managers also track the sales representatives that report to them.

Original Schema (Collapsed Hierarchy Case)

Elements | Details
--- | ---
Title | ACME Sales Force System
Classes | Tables
Person | PERSON
Employee | PERSON, EMPLOYEE
SalesRep | PERSON, EMPLOYEE, REP
Staff | PERSON, EMPLOYEE, STAFF
Client | PERSON, CLIENT
Contact | PERSON, CONTACT

The system suffers from complexity issues that hinder system development and performance. Nearly all queries against the database require large, resource-intensive joins. If you collapse the three-level table hierarchy into a single table, as illustrated in Table: Optimized Schema (Collapsed Hierarchy Case), you substantially reduce system complexity. You eliminate joins from the system, and simplify queries.

Optimized Schema (Collapsed Hierarchy Case)

Elements | Details
--- | ---
Classes | Tables
Person | none
Employee | EMPLOYEE
SalesRep | EMPLOYEE
Staff | EMPLOYEE
Client | CLIENT
Contact | CLIENT

See Also

  Schema Optimization