Object Relational Structural Mapping Guide

Object-Relational Structural Mapping Guide

Six PEAA patterns that bridge between OO domain objects and relational tables: Identity Field, Foreign Key Mapping, Association Table Mapping, Dependent Mapping, Embedded Value, and Serialized LOB.

When to Use

Use this skill when you are:

• Designing how a domain model maps to a relational schema for the first time
• Auditing an existing ORM configuration and schema for structural problems
• Deciding how to map a specific relationship type (1:1, 1:N, N:M, value object, child graph)
• Evaluating whether a JSON/BLOB column is the right choice for nested data
• Debugging N+1 queries, cascade failures, or query-impossible data buried in a LOB

Not for:

• Inheritance hierarchies → use inheritance-mapping-selector
• Data-source gateway selection (Active Record vs Data Mapper) → use data-source-pattern-selector

Context & Input Gathering

Gather before proceeding:

Required:

• List of domain classes with their relationships and cardinalities (1:1, 1:N, N:M)
• Which objects are value objects (no identity, owned by another object) vs. entities (independent identity)
• ORM stack in use (Hibernate/JPA, EF Core, SQLAlchemy, Django, ActiveRecord, TypeORM, or hand-rolled)

Observable from codebase:

• Existing ORM annotations / model definitions (detect from @Entity, models.Model, Column, etc.)
• Schema migrations or DDL files (detect FK patterns, join tables, LOB columns)
• Build files (pom.xml, requirements.txt, *.csproj, Gemfile) to confirm ORM version

Ask if absent:

• "Which classes have independent lifecycles (can be loaded/deleted on their own)?"
• "Are there many-to-many relationships? Do the associations carry their own attributes (e.g., a role or start date)?"
• "Is there any data currently stored as XML, JSON, or binary blob? What SQL queries run against it?"

Sufficiency check: Proceed once you have the domain class list, relationship cardinalities, and a yes/no on value object identity. ORM stack helps with the output but is not blocking.

Process

Step 1 — Identify all domain structures

For each domain class and relationship, classify it into one of these structural types:

WHY: Each structural type maps to exactly one pattern. Misclassifying here leads to wrong pattern choice (e.g., treating a Value Object as an entity creates an unnecessary table and Identity Map entry).

Step 2 — Apply the pattern routing table

WHY: This routing table encodes the core insight that objects and relations have fundamentally different link representations. Without this explicit classification, teams default to wrong choices: adding FK columns for N:M (violates first normal form), creating tables for value objects (unnecessary complexity), or Serialized LOB for data later needing SQL queries (queryability trap).

Step 3 — Apply Identity Field to all entities

For every entity class (not value objects, not dependents):

1. Always prefer a surrogate key (auto-assigned integer or UUID). Meaningful keys (email, SSN, order number) appear stable but fail in practice: human input errors break both uniqueness and immutability.
2. Prefer simple (single-column) keys. They enable a Layer Supertype with uniform key handling. Compound keys require per-class handling and carry implicit meaning that tends to leak.
3. Key type: 64-bit integer (BIGINT) is the best default — fast equality check, fast increment, effectively unlimited range. UUIDs/GUIDs provide database-wide uniqueness at the cost of larger index size and slower inserts (random insertion order).
4. Inheritance caveat: With Class Table or Concrete Table Inheritance, keys must be unique across the hierarchy, not just per-table, to avoid Identity Map collisions.

WHY: The Identity Field is the bridge between the object graph and the relational schema. Without it, you cannot map FK references back to in-memory objects. Without surrogate keys, you inherit the fragility of the real world into your database contract.

Step 4 — Map relationships

Foreign Key Mapping (1:1 and N:1):

• The FK column lives in the table of the class that holds the reference in memory.
• For a collection (1:N), the FK lives in the child table (structural inversion: the parent holds a collection in OO, but the child table holds the FK in SQL).
• Write order: insert parent first, then children, to satisfy FK constraints.

Association Table Mapping (N:M):

• Create a link table with two FK columns (one per side).
• The link table has no corresponding domain object and no Identity Field of its own.
• Its PK is the compound of both FKs.
• Treat the link table like a Dependent Mapping — delete all links for one side and re-insert on update.
• If the association acquires its own attributes (role, start_date), promote the link table to a first-class entity with its own Identity Field and Foreign Key Mappings on both sides.

WHY for Association Table: There is no alternative for N:M in relational databases. Attempts to model N:M with a list-of-IDs column violate first normal form and make queries and updates extremely painful. Even if the association has no attributes today, using a join table preserves schema flexibility for when attributes appear.

Step 5 — Map child structures

Dependent Mapping:

• Apply when: child object has no independent identity, is always loaded with its owner, and is never referenced by foreign keys from other tables.
• The child class has no Identity Field, no Identity Map entry, and no independent finder methods.
• The owner's mapper (or ORM cascade config) handles all inserts, updates, and deletes.
• On update: delete all dependents for the owner, then re-insert (safe because no external FK references exist).
• If another table needs a direct FK to the child, the child is not truly dependent — give it an Identity Field and use Foreign Key Mapping instead.

Embedded Value:

• Apply to all DDD Value Objects (Money, Address, DateRange, GeoPoint, etc.).
• Map each value field as a column on the owner's table (e.g., employment.salary_amount, employment.salary_currency).
• The value class has no persistence methods of its own; the owner saves/loads it.
• Do NOT use Embedded Value if: (a) the value is shared across multiple owners, (b) there can be a variable number of values per owner, or (c) you need to sort/filter on the value's fields via SQL independently of the owner.

WHY for Embedded Value: Value Objects have no identity and should never have their own table — a table of Money values or Address values is meaningless without context. Embedding them preserves the OO semantics (the value is part of the owner, not related to it) while avoiding extra joins and unnecessary tables.

Step 6 — Evaluate any graph / LOB candidates

For complex hierarchical or graph structures:

1. Can the structure be represented with a self-referencing FK (e.g., parent_id on an organization table)? If yes, prefer this — it keeps data queryable.
2. If the structure is truly too complex to normalize, and you are certain SQL will never need to filter or join on the internal fields, consider Serialized LOB.
3. Choose format: JSON/JSONB (PostgreSQL, MySQL 5.7+) is preferred over XML for readability and tooling; binary BLOB is compact but opaque and fragile to class changes.
4. Verify the anti-pattern checklist for Serialized LOB (see Key Principles).

WHY: Serialized LOB sacrifices SQL queryability for schema simplicity. This trade-off is acceptable for truly private, complex, non-queryable subgraphs. It is a trap when applied to data that reporting queries, search, or business logic will need to inspect.

Step 7 — Produce the structural mapping design document

For each entity and relationship, output:

[ClassName / Relationship]
  Pattern: <pattern name>
  Schema: <table/columns/constraints sketch>
  ORM config: <annotation or model field>
  Rationale: <why this pattern fits>
  Anti-pattern warning: <if applicable>

Review cross-cutting concerns:

• Write ordering for inserts (parent before child for all FK relationships)
• Cascade delete configuration (Dependent Mapping → cascade all; FK Mapping → decide per relationship)
• Identity Map interaction (only entities with Identity Field enter the Identity Map; dependents and value objects do not)

Inputs

• Domain model class list with relationships and cardinalities
• Value object identification (which classes lack independent identity)
• ORM stack and version
• Existing schema (if mapping to a pre-existing database)
• Any current LOB/JSON columns and the SQL queries that run against them

Outputs

A structural mapping design document containing:

1. Pattern assignment table — every entity, value object, and relationship mapped to a pattern with rationale
2. Schema sketch — table definitions (columns, types, FK constraints, join tables)
3. ORM configuration — idiomatic annotations/model fields for the detected stack
4. Write-order dependency graph — insert/update ordering to satisfy FK constraints
5. Anti-pattern flags — any meaningful keys, LOB-queryability risks, or orphaned FK references identified

Output template (per structure):

## [Structure Name]
**Pattern:** [Pattern Name]
**Schema:**
  [table_name]([pk] BIGINT PK, [fk] BIGINT FK → [other_table.pk], [field] TYPE, ...)
**ORM:**
  [stack-specific annotation/field declaration]
**Rationale:** [1-2 sentences on why this pattern fits]
**Warning:** [if anti-pattern risk exists]

Key Principles

1. Surrogate keys over meaningful keys — always. Meaningful keys (SSN, email, order number) require uniqueness AND immutability from the real world, which human error and business rule changes routinely violate. Surrogate auto-assigned keys give you control over both. Fowler's framing: "take a rare stand on the side of meaninglessness."

2. Association Table Mapping is the only correct answer for N:M. Any attempt to encode a many-to-many with a list-of-IDs column violates first normal form and will make future queries impossible. Use a join table, even if the association has no attributes today — you will thank yourself when it acquires them.

3. Dependent Mapping requires no-external-FK discipline. A child object only qualifies as a dependent if no other table holds a FK reference to its table. The moment another entity needs a direct reference to the child, the child needs its own Identity Field and becomes a standalone entity mapped via Foreign Key Mapping.

4. All Value Objects should use Embedded Value. DDD Value Objects (Money, Address, DateRange) have no independent identity. Giving them their own table and Identity Field is wrong — it implies identity they don't have and adds joins where none are needed. The owner table absorbs their columns.

5. Serialized LOB is a trap for queryable data. The check is binary: will SQL ever need to filter, sort, or join on data inside the column? If yes, normalize it. PostgreSQL JSONB operators and XPath do not change this calculus — they are not portable and do not perform at scale like indexed normalized columns.

6. The structural inversion rule for 1:N. In OO, the parent holds the collection (album has tracks). In the relational model, the FK is on the child (track.album_id). This inversion is the source of most ORM confusion. The rule: the FK always lives on the "many" side of the relationship, regardless of which direction the OO association points.

7. When a join table acquires attributes, promote it to an entity. The moment a link table needs its own data (start_date, end_date, role, weight), it should become a first-class entity with its own Identity Field and explicit Foreign Key Mappings on both sides. This is a DDD relationship-as-entity promotion.

Examples

Example 1: E-Commerce Order Domain

Trigger: Team is designing the persistence layer for an order management system. Domain classes: Customer, Order, LineItem, Address (value object for shipping and billing).

Process:

• Customer: entity → Identity Field (surrogate customer_id BIGINT)
• Order: entity → Identity Field; references Customer → Foreign Key Mapping (order.customer_id FK → customers.customer_id)
• LineItem: child with no independent identity, only exists within Order → Dependent Mapping (order_id, sequence composite PK or surrogate; cascade all)
• ShippingAddress / BillingAddress: Value Objects → Embedded Value (columns shipping_street, shipping_city, shipping_zip, billing_street, etc. on the orders table)
• Order ↔ Promotion (a customer's Order can use multiple Promotions, and a Promotion applies to multiple Orders): N:M → Association Table Mapping (order_promotions(order_id, promotion_id))

Output:

customers(customer_id BIGINT PK, name VARCHAR)
orders(order_id BIGINT PK, customer_id BIGINT FK, 
       shipping_street VARCHAR, shipping_city VARCHAR, shipping_zip VARCHAR,
       billing_street VARCHAR, billing_city VARCHAR, billing_zip VARCHAR)
line_items(line_item_id BIGINT PK, order_id BIGINT FK, product_id BIGINT FK, 
           quantity INT, unit_price DECIMAL)
order_promotions(order_id BIGINT FK, promotion_id BIGINT FK, PRIMARY KEY(order_id, promotion_id))

LineItem uses cascade-delete. ShippingAddress and BillingAddress are Embedded Value — no join needed to load them. Order↔Promotion uses a join table with no attributes (yet).

Example 2: Music Library with Dependent Tracks

Trigger: Mapping the Artist/Album/Track domain from PEAA Chapter 12.

Process:

• Artist: entity → Identity Field (artist_id BIGINT)
• Album: entity, references Artist → Identity Field + Foreign Key Mapping (album.artist_id FK → artists.artist_id)
• Track: child of Album with no identity outside Album context; no other table references Track directly → Dependent Mapping. Album mapper loads/saves/deletes all Tracks. Track has no independent finder.
• Track has no Identity Field; Album mapper deletes-and-reinserts all Tracks when Album is saved.

Output schema:

artists(artist_id BIGINT PK, name VARCHAR)
albums(album_id BIGINT PK, artist_id BIGINT FK, title VARCHAR)
tracks(album_id BIGINT FK, sequence INT, title VARCHAR, duration INT,
       PRIMARY KEY(album_id, sequence))

ORM (Hibernate): @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true) on Album → tracks. Tracks loaded when Album is loaded.

Example 3: Legacy LOB Anti-Pattern Detection

Trigger: Existing system stores customer contact preferences as XML CLOB in the customers table. Support team needs to query "all customers who prefer email contact." Currently impossible via SQL.

Process:

• Identify: customers.preferences_xml CLOB — a Serialized LOB.
• Apply check: Do SQL queries need to filter on data inside the LOB? Yes — preferred_channel = 'email' must be queryable.
• Verdict: Anti-pattern. Serialized LOB used for queryable data.
• Recommendation: Normalize to a customer_preferences table: (customer_id BIGINT FK, preference_key VARCHAR, preference_value VARCHAR) or (customer_id, channel ENUM, enabled BOOLEAN). Apply Foreign Key Mapping.
• Exception path: If the preference structure is complex and evolving AND a reporting-only database handles the queries, Serialized LOB with JSONB can remain in the operational DB while the reporting DB normalizes the structure.

References

• Pattern Decision Table — Full routing table with edge cases
• ORM Mapping Cheatsheet — Per-pattern ORM config for Hibernate/JPA, EF Core, SQLAlchemy, Django, Rails, TypeORM
• Anti-Pattern Checklist — Detection signals for all 5 structural anti-patterns

License

This skill is licensed under CC-BY-SA-4.0. Source: BookForge — Patterns of Enterprise Application Architecture by Martin Fowler, David Rice, Matthew Foemmel, Edward Hieatt, Robert Mee, Randy Stafford.

Related BookForge Skills

Install related skills from ClawhHub:

• clawhub install bookforge-data-source-pattern-selector
• clawhub install bookforge-inheritance-mapping-selector
• clawhub install bookforge-data-access-anti-pattern-auditor

Or install the full book set from GitHub: bookforge-skills