Refactoring Database Schemas for Module-Specific Tables

We've been working on the Reimpact/platform project, and recently tackled a significant refactoring of our database schema. Our initial design had a mix of shared and module-specific tables within each tenant schema, which led to some challenges as the platform grew.

The primary goal of this refactoring was to improve modularity and maintainability by clearly separating module-specific data.

The Old Approach

Previously, all tables within a tenant schema were either shared across modules (e.g., products, warehouses, brands) or module-specific. This meant a single schema contained a mix of tables with potentially conflicting naming conventions and data structures.

The New Architecture

To address this, we've reorganized the database schema to clearly delineate module ownership. Now, module-specific tables are prefixed with the module name. For example:

• packaging_materials (packaging module)
• textiles_sales (textiles module)
• tires_purchases (tires module)

This approach provides several advantages:

• Clear Ownership: Each module has a clearly defined set of tables.
• Reduced Naming Conflicts: Module prefixes prevent naming collisions between tables in different modules.
• Improved Maintainability: Changes within a module are less likely to impact other modules.

Data Migration

A key part of this refactoring was migrating existing data to the new schema. We employed a data migration strategy that uses priority_productables as a module discriminator for splitting shared MySQL tables. While I can't show you the exact implementation, the general idea looks like this in PHP:

<?php

function migrateProducts(string $moduleName, array $productIds):
 void {
  $productTable = $moduleName . '_products';
  foreach ($productIds as $productId) {
   // Logic to move product data to the new table
   echo "Migrating product {$productId} to table {$productTable}\n";
  }
}

// Example usage:
migrateProducts('textiles', [1, 2, 3]);
?>

This PHP example demonstrates how we might migrate product data from a shared products table to a module-specific table (e.g., textiles_products). The $moduleName parameter determines the destination table.

Conclusion

By adopting a module-specific table architecture, we've improved the modularity and maintainability of the Reimpact/platform project. This refactoring allows us to evolve individual modules with less risk of impacting other parts of the system.