Aggregation

Understanding the theory and notation of aggregation is essential, but the true mastery comes from recognizing where and when to apply it in real-world data modeling scenarios. Aggregation isn't a construct you'll need in every model—but when the scenario calls for it, no simpler construct will suffice.

In practice, aggregation emerges in domains characterized by:

• Multi-layered associations where entities relate to specific pairings of other entities
• Oversight and governance structures where authorities monitor or manage specific relationships
• Resource allocation where budgets, equipment, or permissions are assigned to specific work arrangements
• Contractual and legal frameworks where agreements govern specific associations
• Workflow and approval processes where actions are taken on specific relationship instances

This page explores detailed use cases across multiple domains, helping you recognize aggregation opportunities in your own modeling work.

Domain Context:

Large enterprises manage complex resource allocations. Employees work on projects, but management oversight, budget allocation, and performance tracking apply to specific work assignments—not to employees or projects independently.

Scenario: Project Portfolio Management System

A multinational corporation tracks:

• 10,000+ employees across 50 countries
• 500+ active projects at any time
• Employees may work on multiple projects simultaneously
• Each work assignment has specific hours, roles, and deliverables
• Department managers sponsor work assignments with budget allocations
• Finance tracks costs at the work-assignment level
• HR monitors performance at the work-assignment level

Why Aggregation is Required:

The key insight is that sponsorship, cost tracking, and performance monitoring don't apply to employees or projects in isolation—they apply to specific employee-project pairings.

• If Manager A sponsors Employee X's work on Project Y, that sponsorship doesn't extend to Employee X's work on Project Z
• Finance tracks costs for Employee X on Project Y separately from Employee X on Project Z
• Performance reviews assess Employee X's contribution to Project Y independently

Aggregation Model:

Aggregated: WORK_ASSIGNMENT = (EMPLOYEE works_on PROJECT)

Outer Relationships:
- MANAGER sponsors WORK_ASSIGNMENT
- FINANCE_RECORD tracks WORK_ASSIGNMENT
- PERFORMANCE_REVIEW assesses WORK_ASSIGNMENT

Sample Requirements Mapped to Model:

Queries Enabled by This Model:

• "What is the total budget Thompson sponsors across all work assignments?"
• "Which work assignments lack sponsors?"
• "What is John's performance rating on each of his project assignments?"
• "Which projects have the highest cost per work assignment?"

Domain Context:

Healthcare systems manage complex relationships between patients, treatments, providers, and payers. A patient receiving a specific treatment becomes a distinct entity that insurers cover, auditors review, and quality systems monitor.

Scenario: Hospital Treatment Management System

A hospital network tracks:

• Patients receive treatments (surgeries, therapies, medications)
• Each patient-treatment instance (called a "treatment episode") is distinct
• Insurance companies cover specific treatment episodes
• Treatment episodes may be covered by multiple insurers (primary and secondary)
• Quality auditors review specific treatment episodes for compliance
• Billing generates invoices for specific treatment episodes

Why Aggregation is Required:

Insurance coverage doesn't apply to "Patient X" globally or "Treatment Y" universally. It applies to "Patient X receiving Treatment Y at Time T"—a specific instance of the patient-treatment relationship.

• Patient John's appendectomy on January 15 is covered by Blue Cross at 80%
• Patient John's physical therapy sessions in February are covered by a different policy
• The same treatment given to different patients has different coverage based on individual policies

Aggregation Model:

Inner Relationship: PATIENT receives TREATMENT
  - Attributes: treatment_date, provider_id, diagnosis_code, notes

Aggregated: TREATMENT_EPISODE = (PATIENT receives TREATMENT)

Outer Relationships:
- INSURANCE_POLICY covers TREATMENT_EPISODE
  - Attributes: coverage_percentage, copay_amount, approval_date
- QUALITY_AUDIT reviews TREATMENT_EPISODE
  - Attributes: audit_date, auditor_id, compliance_score
- INVOICE bills_for TREATMENT_EPISODE
  - Attributes: invoice_number, amount, payment_status

Complex Healthcare Scenarios:

Multiple Insurers per Episode:

A treatment episode might have:

• Primary insurance (pays first portion)
• Secondary insurance (pays remainder)
• Patient self-pay (remaining balance)

COVERS(Primary_Policy, (John, Appendectomy, 2025-01-15), coverage=80%)
COVERS(Secondary_Policy, (John, Appendectomy, 2025-01-15), coverage=15%)
SELF_PAY(John, (John, Appendectomy, 2025-01-15), amount=$500)

Time-Based Treatment Series:

Some treatments are ongoing series. Each session is a separate episode:

(John, Chemotherapy, Session1, 2025-03-01) - covered by Policy A
(John, Chemotherapy, Session2, 2025-03-15) - covered by Policy A
(John, Chemotherapy, Session3, 2025-03-29) - covered by Policy B (policy changed)

The treatment relationship includes session identifiers, and each session can have different coverage.

Domain Context:

Supply chain management involves complex relationships between suppliers, products, buyers, and logistics providers. Contracts govern specific supply arrangements, and quality control, logistics tracking, and financial reconciliation apply to specific supply relationships.

Scenario: Manufacturing Supply Chain System

A manufacturing company manages:

• Multiple suppliers providing various parts
• Each supplier-part relationship has negotiated pricing and lead times
• Contracts formalize specific supplier-part arrangements
• Quality inspections apply to specific supplier-part combinations
• Logistics tracks shipments of specific supplier-part combinations
• Finance reconciles invoices against specific supply arrangements

Why Aggregation is Required:

A contract doesn't apply to "Supplier A" generically or "Part X" generically. It formalizes the specific arrangement of "Supplier A providing Part X".

• Supplier A's contract for Part X has different terms than Supplier A's contract for Part Y
• Part X from Supplier A has different quality history than Part X from Supplier B
• Logistics tracks shipments by supplier-part combination, not just by supplier or part

Aggregation Model:

Inner Relationship: SUPPLIER supplies PART
  - Attributes: unit_price, min_order_qty, lead_time_days

Aggregated: SUPPLY_ARRANGEMENT = (SUPPLIER supplies PART)

Outer Relationships:
- CONTRACT governs SUPPLY_ARRANGEMENT
  - Attributes: contract_number, start_date, end_date, terms
- QUALITY_CERT certifies SUPPLY_ARRANGEMENT  
  - Attributes: certification_date, quality_grade, inspector_id
- SHIPMENT delivers_under SUPPLY_ARRANGEMENT
  - Attributes: shipment_id, quantity, ship_date, tracking_number
- INVOICE bills_for SUPPLY_ARRANGEMENT
  - Attributes: invoice_number, amount, payment_terms

Extended Scenario: Three-Way Supply Arrangements

In manufacturing, suppliers often provide parts specifically for particular projects or production runs:

Inner Ternary Relationship: SUPPLIER supplies PART for PROJECT

Aggregated: PROJECT_SUPPLY = (SUPPLIER supplies PART for PROJECT)

Outer Relationships:
- CONTRACT governs PROJECT_SUPPLY
- LOGISTICS tracks PROJECT_SUPPLY

This models scenarios like: "Supplier A provides Engine Bolts for the 2026 Model X production run, governed by Contract #12345, with shipments tracked separately from Supplier A's Engine Bolts for the 2026 Model Y production run."

Domain Context:

Educational institutions manage complex relationships between students, courses, instructors, and funding sources. Scholarships, academic advisors, and grade appeals all relate to specific student-course enrollments.

Scenario: University Course Management System

A university tracks:

• Students enroll in courses each semester
• Each enrollment has its own grade, attendance, and completion status
• Scholarships fund specific enrollments (e.g., STEM scholarship covers only science courses)
• Academic advisors approve specific course enrollments
• Grade appeals are filed for specific enrolled courses
• Teaching assistants support specific courses with enrolled students

Why Aggregation is Required:

• A scholarship doesn't fund "Student A" globally—it funds "Student A's enrollment in Course X"
• A grade appeal isn't against "Course Y"—it's against "Student A's grade in Course Y"
• An advisor doesn't just advise "courses"—they approve "specific student enrollments in specific courses"

Aggregation Model:

Inner Relationship: STUDENT enrolls_in COURSE
  - Attributes: semester, grade, status, hours_attended

Aggregated: ENROLLMENT = (STUDENT enrolls_in COURSE)

Outer Relationships:
- SCHOLARSHIP funds ENROLLMENT
  - Attributes: award_amount, award_date, conditions
- ADVISOR approves ENROLLMENT
  - Attributes: approval_date, recommendations
- GRADE_APPEAL contests ENROLLMENT
  - Attributes: appeal_date, grounds, outcome
- TEACHING_ASSISTANT assists_with ENROLLMENT (for specific student support)
  - Attributes: hours_provided, topics_covered

Complex Scenario: Research Assistantships

Graduate students may work on research projects under faculty supervision, with grants funding specific student-project-faculty combinations:

Inner: STUDENT works_with FACULTY on PROJECT

Aggregated: RESEARCH_APPOINTMENT = (STUDENT works_with FACULTY on PROJECT)

Outer Relationships:
- GRANT funds RESEARCH_APPOINTMENT
  - Attributes: stipend_amount, start_date, end_date
- THESIS_COMMITTEE oversees RESEARCH_APPOINTMENT
  - Attributes: role, appointed_date

Here, the inner relationship is ternary (student-faculty-project), and the aggregation allows grants to fund specific research appointments.

Domain Context:

Financial services involve complex relationships between clients, investment products, advisors, and regulatory bodies. Advisory relationships, compliance reviews, and fee arrangements apply to specific client-investment holdings.

Scenario: Wealth Management Platform

An investment firm manages:

• Clients hold various investment products (stocks, bonds, mutual funds)
• Each client-investment holding has quantity, purchase date, and current value
• Financial advisors manage specific client-investment relationships
• Compliance officers review specific holdings for regulatory adherence
• Fee structures may vary by client-investment combination
• Performance reporting is generated per client-investment holding

Why Aggregation is Required:

• Advisor A manages Client X's tech stock holdings, while Advisor B manages Client X's bond holdings
• Compliance review of Client X's cryptocurrency holdings is separate from review of their traditional stocks
• Fee schedules may differ: Client X pays 1% on managed equities but 0.5% on index funds

Aggregation Model:

Inner Relationship: CLIENT holds INVESTMENT_PRODUCT
  - Attributes: quantity, purchase_date, purchase_price, current_value

Aggregated: HOLDING = (CLIENT holds INVESTMENT_PRODUCT)

Outer Relationships:
- ADVISOR manages HOLDING
  - Attributes: assigned_date, management_strategy, target_allocation
- COMPLIANCE_REVIEW audits HOLDING
  - Attributes: review_date, risk_rating, issues_found
- FEE_SCHEDULE applies_to HOLDING
  - Attributes: fee_percentage, fee_type, billing_cycle
- PERFORMANCE_REPORT covers HOLDING
  - Attributes: report_date, return_percentage, benchmark_comparison

Extended Scenario: Custodian Relationships

Investments may be held at different custodians, creating another layer:

Base: CLIENT holds INVESTMENT at CUSTODIAN

Aggregated: CUSTODIAL_HOLDING = (CLIENT holds INVESTMENT at CUSTODIAN)

Outer Relationships:
- ADVISOR manages CUSTODIAL_HOLDING
- FEE_SCHEDULE applies_to CUSTODIAL_HOLDING

This models scenarios where Client X's Apple stock at Schwab has different management than Client X's Apple stock at Fidelity.

Domain Context:

Modern project management involves complex resource allocation, time tracking, and approval workflows. Team members are assigned to tasks, and managers, clients, and systems need to track, approve, and report on specific assignments.

Scenario: Enterprise Project Management System

A software development company tracks:

• Team members are assigned to specific tasks within projects
• Each assignment has estimated hours, actual hours, and status
• Project managers approve task assignments
• Time tracking systems record hours against task assignments
• Clients can view and comment on specific task assignments
• Code reviews are conducted for specific developer-task assignments

Why Aggregation is Required:

• Manager approval applies to "Developer X assigned to Task Y", not to the developer or task alone
• Time tracking entries are for "Developer X working on Task Y", not general time
• Code review is for "Developer X's code for Task Y", linking reviewer to specific assignment

Aggregation Model:

Inner Relationship: TEAM_MEMBER assigned_to TASK
  - Attributes: assigned_date, estimated_hours, priority

Aggregated: TASK_ASSIGNMENT = (TEAM_MEMBER assigned_to TASK)

Outer Relationships:
- PROJECT_MANAGER approves TASK_ASSIGNMENT
  - Attributes: approval_date, notes, deadline_set
- TIME_ENTRY records_against TASK_ASSIGNMENT
  - Attributes: date, hours, description, billable
- CODE_REVIEW evaluates TASK_ASSIGNMENT
  - Attributes: review_date, reviewer_id, status, comments
- CLIENT_FEEDBACK references TASK_ASSIGNMENT
  - Attributes: feedback_date, satisfaction_score, comments

Workflow Integration:

Aggregation supports workflow states at the assignment level:

1. Assignment created: TASK_ASSIGNMENT(Dev, Task) status = 'Created'
2. Manager approval: APPROVES(Manager, (Dev, Task)) → status = 'Approved'
3. Work begins: TIME_ENTRY records hours → status = 'In Progress'
4. Code review: CODE_REVIEW(Reviewer, (Dev, Task)) → status = 'Review'
5. Client feedback: CLIENT_FEEDBACK(Client, (Dev, Task)) → status = 'Feedback'
6. Completion: status = 'Complete'

Each workflow action relates to the aggregated task assignment.

Having examined multiple use cases, we can now extract general patterns that signal when aggregation is the appropriate modeling choice.

Signal Pattern 1: Governance/Oversight Entities

When entities exist specifically to govern, oversee, or manage relationships between other entities:

• Contracts governing supply relationships
• Managers overseeing work assignments
• Auditors reviewing transaction pairings
• Regulators monitoring business relationships

Question to ask: "Does this entity's purpose center on managing a relationship between other entities?"

Signal Pattern 2: Per-Relationship Resources

When resources (money, time, equipment) are allocated to specific relationship instances:

• Budgets for specific employee-project assignments
• Scholarship funds for specific student-course enrollments
• Equipment allocated to specific worker-shift pairings
• Time tracked per person per task

Question to ask: "Is this resource tied to a specific pairing, not to either entity independently?"

Signal Pattern 3: Relationship-Specific Actions

When actions (approvals, reviews, modifications) target specific relationship instances:

• Approving a specific enrollment
• Reviewing a specific treatment episode
• Modifying a specific placement arrangement
• Canceling a specific booking

Question to ask: "Is the action taken on a specific association between entities, not on either entity alone?"

Signal Pattern 4: Parallel Outer Entities

When multiple different entity types need to relate to the same inner relationship:

• Managers, auditors, and finance all relating to work assignments
• Insurers, quality auditors, and billing all relating to treatment episodes
• Advisors, compliance, and fees all relating to investment holdings

Question to ask: "Do multiple unrelated entities all need to reference the same pairing?"

Signal Pattern 5: Reified Domain Concepts

When the domain naturally talks about the relationship as a 'thing':

• "The assignment" (not just 'John works on Project X')
• "The contract" (not just 'Supplier provides Part')
• "The enrollment" (not just 'Student takes Course')
• "The placement" (not just 'Consultant at Client')

Question to ask: "Do domain experts refer to the relationship with a noun, as if it were a tangible thing?"

We've explored diverse real-world scenarios where aggregation provides the appropriate modeling solution. Let's consolidate the key takeaways:

What's next:

Now that we understand when to use aggregation conceptually, the final page covers mapping aggregation to the relational model—how to translate aggregation constructs into actual database tables with appropriate keys, foreign keys, and constraints.