Inter-Company Data Isolation and Tenant Architecture in ERP Database Design

When developing custom enterprise software for holding structures or group companies comprising multiple legal entities, data security and isolation strategies rank among the most critical architectural decisions. In Enterprise Software & ERP processes, the correct model must be selected to prevent inter-company data leakage and ensure regulatory compliance.

ERP Architecture and Multi-Company Structure

When designing the ERP architecture for group companies, the primary objective is to maintain operational independence while bringing the operations of different legal entities under the same digital umbrella. One of the most fundamental concepts encountered at this point is the tenant structure. In multi-company or multi-tenant scenarios, there are three main approaches that can be followed during the database design phase: separate databases, schema-based isolation, and Row-Level Security (RLS).

1. Separate Database Approach

In this method, a completely independent database is created physically or logically for each company.

• Advantages: Provides the highest level of data isolation. An error or security breach occurring in one company's database does not directly affect the others.
• Disadvantages: Inter-company data transfers and joint consolidated reporting are costly and require additional integration layers.

2. Schema-Based Isolation

On the same database server, a separate database schema is defined for each company. The table structures are identical, but the schemas are isolated.

• Advantages: Reduces shared maintenance costs while offering query flexibility across schemas.
• Disadvantages: The complexity of dynamic schema management during the database design phase can create an additional burden for development teams.

3. Row-Level Security (RLS)

All companies' data is kept within the same tables, but a company identifier (tenant id) is added to each row. Thanks to rules at the database level, users can only see the rows they are authorized to view.

• Advantages: Minimizes storage costs and makes consolidated reporting possible with a single query.
• Disadvantages: Strict authorization testing must be performed at the application and database layers to prevent security vulnerabilities.

Shared Master Data Management and Decision-Making Processes

In enterprise software projects, the choice of isolation model depends on the degree of interdependence in companies' business processes, audit requirements, and IT infrastructure budgets. How shared master data, such as material cards and current accounts, will be shared must be clarified during the early stages of architectural design.

Hypothetical Scenario: Three-Company Holding Structure and RLS Implementation

In this scenario, we examine how the Row-Level Security (RLS) model is configured for a holding company with three distinct legal entities operating in manufacturing, logistics, and retail. Holding management has decided on a single database and shared tables to lower all operational costs and obtain real-time consolidated profitability reports. The development team started by adding a 'tenant_id' column to the invoices, orders, and stock movements tables. When the manufacturing company (Tenant A) logs into the system, database-level security policies take effect, filtering only the rows with the Tenant A ID value independently of the application layer. Consequently, retail company employees cannot access production data in any way.

However, this architecture brings serious trade-offs in terms of trade and performance. If correct indexing is not performed on a stock movements table containing millions of rows, the 'WHERE tenant_id = X' condition automatically added to every query strains the database engine and increases execution plan costs. From an error-management perspective, disaster scenarios can occur if the tenant context sent from the application layer is forgotten or set incorrectly; because if RLS rules are bypassed, all companies' data can get mixed up. To eliminate these risks, database connection pooling management must be strictly audited, and automated penetration tests should be run during every sprint period.

ERP Database Isolation Implementation Checklist

To ensure that the tenant architecture works flawlessly and data leaks are prevented in enterprise software projects, executing the following steps completely is of critical importance:

1. Legal and Financial Requirement Analysis: Local data retention laws of the countries where companies operate and whether they mandate separate databases must be reported in advance.
2. Tenant ID Standardization: Tenant columns with optimized data types compatible with primary key structures (e.g., UUID or Integer) must be defined in all tables.
3. Coding Database Policies: Row-level security (RLS) or schema-based permissions must be secured directly at the database level (stored procedures and security policies) rather than leaving them to application code.
4. Connection Pooling and Context Management: Database sessions must be reset after each request to prevent session state or connection context leaks in multi-tenant architectures.
5. Comprehensive Integration and Load Tests: It must be tested whether isolation mechanisms degrade performance under high-volume data requests coming concurrently from different tenants.

This checklist provides a roadmap to minimize architectural risks that may be encountered in Enterprise Software & ERP projects and to complete audit processes smoothly.

Sources

• Enterprise Software & ERP — Cy Logic Software