Cost Allocation and SQL Optimization in Multi-Company ERP Architectures

In holding company structures with multiple legal entities, the fair and automatic allocation of shared expenses and central costs to subsidiaries is a critical process. In Enterprise Software & ERP solutions, setting up these processes correctly at the database layer directly affects the duration of periodic closing processes and data integrity. In this technical guide, we examine cost allocation processes and SQL optimization methods within multi-company structures.

1. Multi-Company Architecture Design at the Database Layer

Multi-company structures are handled in database design with two fundamental approaches: tenant-based separation within a single database or separate schemas for each company. Using a single-table or centralized calculation layer in cross-operations such as the distribution of shared expenses provides advantages in terms of maintaining data consistency.

• Master data of legal entities should be kept in a centralized table.
• Distribution keys (number of personnel, turnover, square meters, etc.) must be stored in periodically updatable tables.
• Foreign key constraints should be configured to prevent the entry of inconsistent financial transactions between companies.

2. Structuring Cost Allocation Algorithms with SQL

Allocating centralized costs to subsidiaries requires complex matrix calculations. When this process is executed at the application tier, it can cause high network traffic. Therefore, it is recommended to perform calculations inside the database, where the data resides (in-database processing).

• Define temporary tables or table variables that calculate distribution ratios.
• Prefer set-based T-SQL queries instead of cursors to reduce processing costs.
• Protect ACID rules by gathering the steps that convert distribution results into accounting vouchers within a single transaction.

3. SQL Optimization and Periodic Closing Performance

Millions of rows of financial data are processed during month-end or year-end periodic closings. Queries without SQL optimization can lead to system locks and timeout errors. To improve performance, you can apply the following techniques:

• Indexing: Add appropriate non-clustered indexes on company ID, period, and account code columns used in cost allocation queries.
• Statistics Updates: Use the UPDATE STATISTICS command after large-volume temporary tables to ensure the query optimizer selects the correct execution plan.
• Partitioning: Minimize table scan costs by partitioning high-volume journal and transaction tables on a periodic basis.

4. Data Integrity and Error Management

In enterprise software architectures, the system must not be left halfway in situations such as power outages, insufficient budget items, or missing data that may occur during distribution. By using TRY...CATCH blocks within the stored procedure, ROLLBACK all database operations in case of erroneous states and store error logs in a centralized table.

5. Hypothetical Case Study and Scenario Analysis

Let us assume that the IT and human resources shared expenses of the holding center operating within the holding, amounting to 1,200,000 TRY, are fairly distributed among three different subsidiaries (Company A, Company B, Company C). In this scenario, the weighted average of the number of personnel, total assets, and monthly turnover data is used as the distribution key. The process is executed via a stored procedure running within the database. In the first step, the total amount for the relevant period is retrieved from the central cost table and assigned to a temporary table variable. Then, a set-based update query is executed by calculating the current distribution ratios of the subsidiaries. From a trade-off analysis perspective, performing the calculation in the database keeps network traffic close to zero while temporarily increasing the CPU and memory consumption of the database server. On the error management side, if any subsidiary's distribution key is entered as zero or missing, the system transfers the relevant item to the manual review queue without stopping the transaction volume and drops a warning record into the operation log table. Thus, periodic closing processes are completed without interruption, and financial audit trails are stored completely.

6. Detailed Implementation Checklist

A checklist containing critical steps to be followed before taking cost allocation and SQL optimization projects live minimizes operational risks. First, ensure that company-based schema or tenant separations are protected by foreign key constraints during the database design stage. Second, define periodic data validation constraints on the tables where distribution keys are kept. In the third stage, verify that periodic partition strategies have been activated and execution plan tests have been performed for high-volume journal and transaction tables. For performance optimization, confirm via code review that the correct non-clustered indexes are created on frequently used queries and cost calculation scripts are written with set-based logic. Fourth, test the ROLLBACK scenarios of transaction mechanisms surrounded by TRY...CATCH blocks against possible system outages or deadlock situations. In the fifth stage, ensure that all these processes are documented in accordance with Enterprise Software & ERP standards and the authorization matrix is updated. This disciplined approach guarantees long-term system stability.

Sources

• Enterprise Software & ERP — Cy Logic Software