Skip to main content

MDM Migration Plan

Objective: To refactor the existing product-centric database schema to a flexible, Multi-Domain Master Data Management (MDM) model, starting with the products table.

Guiding Principles:

  • No Data Loss: Existing data in the products table will be migrated.
  • Maintain Integrity: Foreign key relationships will be preserved and updated.
  • Sequential & Reversible: The process will be broken into a series of Knex migrations, each with a clear up and down method.

Phase 1: Create the New MDM Core Tables​

This phase establishes the foundational tables of the new architecture. We will create three new tables.

  1. domain_schemas

    • Purpose: To govern the structure and data types of the flexible JSONB attributes in core_entities.
    • Action: Create a migration file for a domain_schemas table with columns like entity_type, attribute_name, data_type, and is_required.

  2. core_entities

    • Purpose: The new central table for all master data, replacing the products table.
    • Action: Create a migration file for the core_entities table. It will use the "Hybrid Model":
      • Native Columns: Critical, frequently-queried fields like entity_id, tenant_id, entity_type, entity_name, sku, and unit_cost.
      • JSONB Column: A domain_attributes column for other, less critical product fields (e.g., product_family).
    • A database trigger will be included to validate the JSONB data against the domain_schemas table.

  3. entity_relationships

    • Purpose: A generic table to model all hierarchies and connections, such as Bill of Materials (BOMs).
    • Action: Create a migration file for an entity_relationships table with columns like source_entity_id, target_entity_id, and relationship_type.

Phase 2: Migrate Existing Product Data​

This phase moves the data from the old products table into the new core_entities table.

  1. Data Migration Script
    • Purpose: To transfer all records from products into core_entities.
    • Action: Create a data-only migration file that will:
      1. Read every row from the products table.
      2. For each product, create a new row in the core_entities table, setting entity_type to 'PRODUCT'.
      3. Map the columns from products to their corresponding new columns or into the domain_attributes JSONB field in core_entities.

Phase 3: Update Foreign Key Relationships​

This phase ensures that other tables in the database correctly point to the new core_entities table instead of the old products table.

  1. Update Foreign Keys
    • Purpose: To maintain referential integrity.
    • Action: Create a migration file that uses alterTable to update the foreign key constraints on the following tables:
      • sop_plan_data
      • insights
    • Their product_id foreign keys will be modified to point to core_entities(entity_id).

Phase 4: Decommission the Old products Table​

This is the final cleanup step.

  1. Drop products Table
    • Purpose: To remove the now-redundant products table.
    • Action: Create a final migration file that drops the products table. The down method in this migration will be written to fully re-create the table and its data for safe rollbacks.