Skip Headers

# Schema Case 2: Splitting One Table Into Many

Previous

Next

To improve overall performance of the system, split large tables into two or more smaller tables. This significantly reduces the amount of data traffic required to query the database.

For example, the system illustrated in Table: Original Schema (Splitting One Table into Many Case) assigns employees to projects within an organization. The most common operation reads a set of employees and projects, assigns employees to projects, and update the employees. The employee's address or job classification is also occasionally used to determine the project on which the employee is placed.

Original Schema (Splitting One Table into Many Case)

Elements | Details | Instance Variable | Mapping | Target
--- | --- | --- | --- | ---
Title | ACME Employee Workflow System |  |  |
Classes | Employee, Address, PhoneNumber, EmailAddress, JobClassification, Project |  |  |
Tables | EMPLOYEE, PROJECT, PROJ_EMP |  |  |
Relationships | Employee | address | aggregate | Address
| Employee | phoneNumber | aggregate | EmailAddress
| Employee | emailAddress | aggregate | EmailAddress
| Employee | job | aggregate | JobClassification
| Employee | projects | many-to-many | Project

When you read a large volume of employee records from the database, you must also read their aggregate parts. Because of this, the system suffers from general read performance issues. To resolve this, break the EMPLOYEE table into the EMPLOYEE, ADDRESS, PHONE, EMAIL, and JOB tables, as illustrated in Table: Optimized Schema (Splitting One Table into Many Case).

Because you usually read only the employee information, splitting the table reduces the amount of data transferred from the database to the client. This improves your read performance by reducing the amount of data traffic by 25 percent.

Optimized Schema (Splitting One Table into Many Case)

Elements | Details | Instance Variable | Mapping | Target
--- | --- | --- | --- | ---
Title | ACME Employee Workflow System |  |  |
Classes | Employee, Address, PhoneNumber, EmailAddress, JobClassification, Project |  |  |
Tables | EMPLOYEE, ADDRESS, PHONE, EMAIL, JOB, PROJECT, PROJ_EMP |  |  |
Relationships | Employee | address | one-to-one | Address
| Employee | phoneNumber | one-to-one | EmailAddress
| Employee | emailAddress | one-to-one | EmailAddress
| Employee | job | one-to-one | JobClassification
| Employee | projects | many-to-many | Project

See Also

  Schema Optimization