# SAP Relationship Mapping

> Source: https://docs.synthesized.io/tdk/latest/user_guide/070_integrations/sap/relationship_mapping
> For the complete documentation index, see [llms.txt](https://docs.synthesized.io/llms.txt).

How the Synthesized Platform maintains SAP table relationships through module knowledge and configurable virtual foreign keys.

## The SAP Relationship Challenge

SAP systems present unique challenges for data management:

### No Explicit Foreign Keys

Unlike typical relational databases, SAP tables rarely define foreign key constraints at the database level:

```sql
-- SAP HANA shows no FK constraints
SELECT * FROM SYS.REFERENTIAL_CONSTRAINTS
WHERE SCHEMA_NAME = 'SAP_SCHEMA';
-- Result: Empty or minimal results
```

**Why?**

- Performance optimization in high-volume OLTP systems
- Flexibility for customizations (Z-tables)
- Historical design decisions from R/3 era

### Implicit Relationships

Relationships exist through naming conventions and field semantics:

```
MARA.MATNR ←→ MAKT.MATNR  (Material master to descriptions)
MARA.MATNR ←→ MARC.MATNR  (Material master to plant data)
MARC.WERKS ←→ T001W.WERKS (Plant to plant master)
```

These are "virtual" foreign keys - understood by SAP applications but not enforced by the database.

## How the Platform Handles This

### Module-Specific Knowledge

The platform includes pre-configured knowledge of SAP table structures:

#### MM Module Relationships

```
MARA (Material Master - Root)
│
├── MANDT + MATNR ─────────────┬── MAKT (Descriptions)
│                              ├── MARC (Plant Data)
│                              ├── MARD (Storage Location)
│                              ├── MBEW (Valuation)
│                              ├── MARM (Units of Measure)
│                              └── MVKE (Sales Data)
│
└── MANDT + MATNR ─────────────── MKPF/MSEG (Documents)
```

### Automatic Table Ordering

The platform automatically orders tables for correct data flow:

```yaml
# Tables are processed in dependency order:
tables:
  # 1. Root table first (MARA)
  - table_name_with_schema: "SCHEMA.MARA"

  # 2. Dependent tables follow
  - table_name_with_schema: "SCHEMA.MAKT"  # depends on MARA
  - table_name_with_schema: "SCHEMA.MARC"  # depends on MARA
  - table_name_with_schema: "SCHEMA.MARD"  # depends on MARC
```

### Referential Integrity Preservation

When transforming data, the platform ensures:

1. **Key Fields Match** - MATNR values are consistent across tables
2. **Master Data Exists** - Parent records present before child records
3. **Cascading Updates** - Changes propagate to related tables

## Virtual Foreign Key Definitions

### MM Module Virtual FKs

   
| Child Table | Child Fields | Parent Table | Parent Fields |
| --- | --- | --- | --- |
| 
MAKT

 | 

MANDT, MATNR

 | 

MARA

 | 

MANDT, MATNR

 |
| 

MARC

 | 

MANDT, MATNR

 | 

MARA

 | 

MANDT, MATNR

 |
| 

MARD

 | 

MANDT, MATNR, WERKS

 | 

MARC

 | 

MANDT, MATNR, WERKS

 |
| 

MBEW

 | 

MANDT, MATNR

 | 

MARA

 | 

MANDT, MATNR

 |
| 

MARM

 | 

MANDT, MATNR

 | 

MARA

 | 

MANDT, MATNR

 |
| 

MVKE

 | 

MANDT, MATNR

 | 

MARA

 | 

MANDT, MATNR

 |
| 

MSEG

 | 

MANDT, MBLNR, MJAHR

 | 

MKPF

 | 

MANDT, MBLNR, MJAHR

 |

### Key Field Patterns

SAP uses consistent patterns for relationships:

  
| Pattern | Description | Example |
| --- | --- | --- |
| 
**MANDT**

 | 

Client/mandant - always first in composite keys

 | 

MANDT = '100'

 |
| 

**Primary Business Key**

 | 

Main object identifier

 | 

MATNR, LIFNR, KUNNR

 |
| 

**Organizational Keys**

 | 

Org structure identifiers

 | 

WERKS, BUKRS, VKORG

 |
| 

**Document Keys**

 | 

Document identification

 | 

MBLNR + MJAHR, BELNR + GJAHR

 |

## Configuration Options

### Using Predefined Relationships

The SAP Workflow Wizard automatically applies known relationships:

```yaml
# Automatically generated - no manual configuration needed
tables:
  - table_name_with_schema: "SCHEMA.MARA"
    transformations:
      - columns: ["MANDT", "MATNR"]
        params:
          type: passthrough  # Preserve keys for relationships

  - table_name_with_schema: "SCHEMA.MAKT"
    transformations:
      - columns: ["MANDT", "MATNR"]
        params:
          type: passthrough  # Match parent keys
```

### Custom Virtual Foreign Keys

For custom tables (Z-tables) or non-standard relationships, define virtual FKs:

```yaml
virtual_foreign_keys:
  - table_schema: "SCHEMA"
    table_name: "ZCUSTOM_TABLE"
    columns:
      - "MANDT"
      - "MATNR"
    referenced_table_schema: "SCHEMA"
    referenced_table_name: "MARA"
    referenced_columns:
      - "MANDT"
      - "MATNR"
```

### Foreign Key Generator

For explicit foreign key handling:

```yaml
tables:
  - table_name_with_schema: "SCHEMA.ZCUSTOM_CHILD"
    transformations:
      - columns: ["MANDT", "MATNR"]
        params:
          type: foreign_key_generator
          referred_schema: "SCHEMA"
          referred_table: "MARA"
          referred_fields: ["MANDT", "MATNR"]
```

## Module Filtering

### How It Works

The platform filters tables based on selected modules:

```
Selected Module: MM

Included Tables:
  ✓ MARA (Material Master)
  ✓ MAKT (Material Descriptions)
  ✓ MARC (Plant Data)
  ✗ KNA1 (Customer Master - not MM)
  ✗ LFA1 (Vendor Master - not MM)
```

### Cross-Module References

Some tables are shared across modules:

  
| Table | Used By | Type |
| --- | --- | --- |
| 
T001

 | 

All modules

 | 

Company Code Master

 |
| 

T001W

 | 

MM, SD, PP

 | 

Plant Master

 |
| 

T024

 | 

MM, FI

 | 

Purchasing Group

 |

The platform includes shared tables automatically when needed.

## Handling Complex Relationships

### Many-to-Many Relationships

SAP uses intermediate tables for M:N relationships:

```
MARA ←→ KNMT ←→ KNA1
(Material)  (Cross-Ref)  (Customer)
```

The platform handles these through proper table ordering and key preservation.

### Self-Referencing Tables

Some SAP tables reference themselves:

```
MARA.SATNR → MARA.MATNR  (Configurable material reference)
```

These are handled with careful insert ordering and nullable field handling.

### Circular Dependencies

The platform detects and resolves circular references:

```yaml
cycle_resolution_strategy: FAIL  # or BREAK_CYCLE
```

## Validation

### Pre-Workflow Validation

Before running, the platform validates:

1. All referenced tables exist
2. Key fields have compatible types
3. Relationship chains are complete

### Post-Workflow Validation

After running, verify relationships:

```sql
-- Verify MAKT records match MARA
SELECT COUNT(*) AS orphan_descriptions
FROM TARGET.MAKT t
WHERE NOT EXISTS (
    SELECT 1 FROM TARGET.MARA m
    WHERE m.MANDT = t.MANDT AND m.MATNR = t.MATNR
);

-- Should return 0
```

## Troubleshooting

### Missing Parent Records

**Symptom:** Child records reference non-existent parents

**Cause:** Time slicing excluded older master data

**Solution:** \* Ensure referential integrity is enabled \* Extend time slice period \* Verify master data filter includes referenced records

### Duplicate Key Errors

**Symptom:** Unique constraint violation on insert

**Cause:** Table already contains data

**Solution:** \* Use `table_truncation_mode: TRUNCATE` \* Or use `schema_creation_mode: DROP_AND_CREATE`

### Relationship Not Recognized

**Symptom:** Related data not properly linked

**Cause:** Custom table not in module definition

**Solution:** \* Add virtual foreign key definition \* Use `foreign_key_generator` transformation

## Best Practices

### Start with Standard Tables

1. Use wizard-generated configurations first
2. Validate standard table relationships work
3. Add custom tables incrementally

### Document Custom Relationships

For Z-tables and custom configurations:

- Document the business relationship
- Add virtual FK definitions
- Test with sample data

### Test Referential Integrity

Always validate after workflows:

- Run integrity check queries
- Test SAP transactions
- Verify report data consistency

## See Also

- [Virtual Foreign Keys](https://docs.synthesized.io/tdk/latest/user_guide/020_guides/managing_data_properties/virtual_foreign_keys)
- [SAP Modules](https://docs.synthesized.io/tdk/latest/user_guide/070_integrations/sap/modules)
- [Data Scoping](https://docs.synthesized.io/tdk/latest/user_guide/070_integrations/sap/data_scoping)