Schema Cleanup: Removing Unused Fields For Efficiency

In this article, we delve into the crucial topic of schema cleanup, specifically focusing on the removal of unused fields from the actionGoalContributions category. This process is essential for maintaining a clean, efficient, and understandable database structure. By eliminating unnecessary elements, we can improve performance, reduce complexity, and ensure that our data model accurately reflects the current needs of our system.

Problem: Unused Fields in actionGoalContributions

The actionGoalContributions table currently contains two fields, contributionAmount REAL and measureId TEXT, which are consistently NULL and serve no practical purpose. This discrepancy between the database structure and its actual usage raises concerns about efficiency and maintainability. It's important to address this issue to prevent confusion and streamline our data model.

Current State (v0.6.5)

Our analysis of the current state, based on version 0.6.5 of our system, reveals the following:

Database Evidence:

• The actionGoalContributions table comprises 193 records.
• Critically, none of these records contain non-NULL values for contributionAmount.
• Similarly, no records have non-NULL values for measureId.

Code Evidence:

The ActionCoordinator class, responsible for creating ActionGoalContribution objects, explicitly sets both contributionAmount and measureId to nil during the creation process. This is illustrated in the following Swift code snippet:

let contributions = formData.goalContributions.map { goalId in
    ActionGoalContribution(
        actionId: action.id,
        goalId: goalId,
        contributionAmount: nil,  // ← Always nil
        measureId: nil,           // ← Always nil
        createdAt: Date(),
        id: UUID()
    )
}

This code snippet unequivocally demonstrates that these fields are intentionally set to nil, confirming their lack of current utility. By streamlining the schema to match the actual requirements, you improve data integrity and reduce potential errors. Streamlining also simplifies database management and maintenance.

Historical Context: Why These Fields Exist

The presence of contributionAmount and measureId can be traced back to an initial design concept that envisioned a more granular approach to contribution tracking. The original intent was to enable the system to record and manage how a single action's measure could be divided across multiple goals.

• Example: A user performs a "10km run." The system could then record a contribution of 5km to Goal₁ and another 5km to Goal₂.

However, the system evolved, adopting an auto-matching mechanism that rendered these fields obsolete. This shift in architectural design necessitates a re-evaluation of the database schema to align it with current practices. Furthermore, a well-maintained database will perform better and is easier to troubleshoot and scale as needed.

Auto-Match Pattern: The Current System

The current system relies on an auto-match pattern for contribution tracking. Actions record measurements in the measuredActions table, such as 10km, 30min, or 500kcal. Goals, in turn, specify the measures they track through the expectationMeasures table. Contributions are then automatically calculated through a JOIN operation based on measureId. Below is an example SQL query:

-- Cycling action has 3 measures (Km, Minutes, Kcal)
-- Fitness goal tracks Kcal only
-- Auto-match via JOIN → only Kcal flows to goal

SELECT ma.value, m.title
FROM actionGoalContributions agc
JOIN measuredActions ma ON agc.actionId = ma.actionId
JOIN goals g ON agc.goalId = g.id
JOIN expectationMeasures em ON g.expectationId = em.expectationId
  AND ma.measureId = em.measureId
JOIN measures m ON ma.measureId = m.id
WHERE agc.actionId = ? AND agc.goalId = ?

Result: In this scenario, the Kcal measure from the cycling action flows to the fitness goal. Although the Km and Minutes measures are stored, they are not matched, making them available for future goals or analytics. This auto-match pattern effectively manages contributions without requiring the contributionAmount and measureId fields in actionGoalContributions.

Options for Addressing the Issue

We have identified three potential options for addressing the presence of unused fields in the actionGoalContributions table. Each option presents a unique set of benefits and trade-offs, which must be carefully considered before making a final decision.

Option A: Simplify Schema (Recommended)

This option proposes the direct removal of the unused fields from the actionGoalContributions table. This is done with the following SQL commands:

ALTER TABLE actionGoalContributions DROP COLUMN contributionAmount;
ALTER TABLE actionGoalContributions DROP COLUMN measureId;

Simplified table:

CREATE TABLE actionGoalContributions (
    id TEXT PRIMARY KEY,
    actionId TEXT NOT NULL,
    goalId TEXT NOT NULL,
    createdAt TEXT NOT NULL,
    FOREIGN KEY (actionId) REFERENCES actions(id) ON DELETE CASCADE,
    FOREIGN KEY (goalId) REFERENCES goals(id) ON DELETE CASCADE
);

Benefits:

• ✅ Aligns with Actual Usage: This option ensures that the database schema accurately reflects the current usage pattern, where actionGoalContributions functions as a pure junction table.
• ✅ Prevents Data Loss: Because the fields are consistently NULL, their removal will not result in any data loss.
• ✅ Enhances Clarity: By eliminating unused fields, the schema becomes more semantic, clearly indicating that an action contributes to a goal, with measures auto-matched.
• ✅ Simplifies Migration: The reduction in the number of fields simplifies the migration process, reducing the complexity of explaining the changes.

Trade-offs:

• ❌ Limited Future Flexibility: Removing the fields would prevent the implementation of fine-grained contribution splitting in the future without re-adding the fields. Adding proper indexes, constraints, and data validation rules can prevent the database from becoming corrupted.

Option B: Keep Fields for Future Use

This option suggests retaining the current schema while documenting the intended future use of the contributionAmount and measureId fields.

• Add schema comments explaining the potential future use case.
• Maintain the fields as nullable, with a default value of NULL.
• Implement a user interface (UI) for fine-grained contribution management when or if the need arises.

Benefits:

• ✅ Future-Proofing: This option preserves the potential for advanced use cases in the future.
• ✅ No Migration Required: No immediate changes to the database schema are necessary.

Trade-offs:

• ❌ Increased Complexity: The presence of unused fields adds complexity to the schema.
• ❌ Potential Confusion: Developers might be confused by the existence of fields that are consistently NULL.
• ❌ Overhead: Maintaining unused fields can lead to overhead in terms of CloudKit synchronization and migration efforts.

Option C: Implement Fine-Grained Contributions

This option proposes populating the contributionAmount and measureId fields with actual data, enabling fine-grained contribution tracking.

• Update the ActionFormData to allow users to specify contribution amounts per goal.
• Develop a UI that allows users to allocate measures to specific goals.
• Example: A 60-minute meditation session could be allocated as "1 occasion" to a specific goal rather than tracking the duration in minutes.

Benefits:

• ✅ Maximum Flexibility: This option offers the greatest flexibility for managing contributions.
• ✅ Advanced Goal-Tracking: It supports advanced goal-tracking patterns.

Trade-offs:

• ❌ Significant Complexity: Implementing this option would require significant UI/UX development.
• ❌ Unproven Use Case: There is no proven need for this level of granularity, as demonstrated by the fact that it has not been required in over 200 actions.
• ❌ Violates Data Integrity: This approach could violate the principle that measured data should not be transformed.

Recommended Solution: Option A (Simplify)

After careful consideration of the available options, we recommend Option A: Simplify Schema. This recommendation is based on the following rationale:

1. Elegant Current Usage Pattern: The auto-matching mechanism effectively handles multi-goal, multi-measure scenarios without data duplication.
2. Lack of Proven Use Case: There is no demonstrated need for fine-grained splitting in real-world contributions.
3. Data Transformation Concerns: Transforming measured data using contributionAmount is not appropriate. Instead, accurate data should be recorded directly.
4. YAGNI Principle: We should avoid building for hypothetical future needs.

Migration Path:

The migration path for implementing Option A involves the following steps:

-- Phase 1: Verify no data loss (all are NULL anyway)
SELECT COUNT(*) FROM actionGoalContributions 
WHERE contributionAmount IS NOT NULL OR measureId IS NOT NULL;
-- Expected: 0

-- Phase 2: Drop columns (before v1.0.0)
ALTER TABLE actionGoalContributions DROP COLUMN contributionAmount;
ALTER TABLE actionGoalContributions DROP COLUMN measureId;

Phase 3: Update the ActionGoalContribution model by removing the contributionAmount and measureId fields from the Swift struct.

In the event that fine-grained contributions are required in the future, the fields can be re-added. This approach is backwards compatible, as new fields default to NULL, and existing data will remain unaffected.

Impact Analysis

Implementing Option A will require updates to the following files:

1. swift/Sources/Database/Schemas/schema_current.sql: Remove fields from CREATE TABLE.
2. swift/Sources/Models/Composits/ActionGoalContribution.swift: Remove Swift properties.
3. swift/Sources/Services/Coordinators/ActionCoordinator.swift: Simplify initialization.
4. swift/Sources/Services/Coordinators/FormData/ActionFormData.swift: Update comments.
5. swift/Sources/Services/Repositories/ActionRepository.swift: Remove unused columns from queries.

Testing:

• ✅ Verify that the auto-match JOIN operation continues to function correctly.
• ✅ Ensure that CSV import/export functionality remains unaffected.
• ✅ Confirm that CloudKit synchronization handles the schema change appropriately.

The estimated timeline for completing these tasks is 1-2 hours.

Decision Needed Before v1.0.0

This schema design decision should be finalized before the public launch of v1.0.0 to avoid migration burdens associated with schema changes after users have data. Simplifying the schema now will result in a cleaner architecture for v1.0.0. The target for resolving this issue is v0.7.0.

This article explored the importance of database schema cleanup by removing unused fields and maintaining a well-structured system. By adopting Option A, we can simplify our schema, improve performance, and ensure our data model accurately reflects our system's current needs.

For more in-depth information on database schema design and management, visit Database Design Fundamentals.