Object Relational Structural Mapping Guide
by @quochungto
Object-relational mapping structural patterns guide. Use when designing or auditing how domain objects map to relational tables — identity fields, foreign ke...
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_id BIGINT)order.customer_id FK → customers.customer_id)order_id, sequence composite PK or surrogate; cascade all)shipping_street, shipping_city, shipping_zip, billing_street, etc. on the orders table)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_id BIGINT)album.artist_id FK → artists.artist_id)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:
customers.preferences_xml CLOB — a Serialized LOB.preferred_channel = 'email' must be queryable.customer_preferences table: (customer_id BIGINT FK, preference_key VARCHAR, preference_value VARCHAR) or (customer_id, channel ENUM, enabled BOOLEAN). Apply Foreign Key Mapping.clawhub install bookforge-object-relational-structural-mapping-guide