E-commerce Returns & RMA data model
Third-normal-form mirror of an e-commerce returns / RMA stack as it lands from the OMS, returns platform and 3PL. Sales order header/line shape is mirrored by return_request → return_line; carrier-tracked return shipments are isolated for in-transit visibility; inspection_grade joins to a disposition lookup to drive reverse-logistics economics and recommerce; refund vs exchange settlement is split so revenue and COGS impact remain queryable. Customer name, email, phone and addresses are PII-masked under GDPR/CCPA, and payment processor references are PCI-scoped.
Normalized replica of e-commerce reverse-logistics operations: customer initiates a return request against a sales order, an RMA is approved and a return label is issued via a carrier, the parcel is tracked in transit, received and graded at the returns facility, assigned a disposition (restock/repair/refurbish/liquidate/scrap), and settled via refund or exchange. Models the full Loop/Returnly/Narvar/AfterShip-style RMA flow with carrier tracking, inspection grading, fraud scoring and split returns. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 17
- tables
- 165
- columns
- 25
- relationships
- 7
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
RMA header → line mirrors sales order header → line
Returns are issued against specific order lines with their own quantities, reasons and refund amounts; modelling return_request → return_line at the same grain as sales_order → sales_order_line keeps reconciliation lossless and lets partial returns (subset of lines / quantities) be expressed natively without denormalization.
Return shipments separated from the RMA header
A single RMA can be split across multiple parcels, dropped off at a locker, or re-shipped after a failed pickup; isolating return_shipment with its own tracking number, label cost and carrier-status timeline preserves in-transit visibility without bloating the RMA header.
Inspection grading joined to a disposition lookup
Reverse-logistics economics depend on what actually happens to the item — restock, refurbish, liquidate or scrap each carry different recovery percentages; pairing inspection_grade with a disposition lookup makes recommerce yield and shrink directly queryable per returned unit.
Refund and exchange settlement modelled as separate entities
Refunds hit the original tender (revenue reversal) while exchanges spawn a downstream sales order (revenue retention); keeping refund_event and exchange_order as separate entities means finance can attribute revenue / COGS impact cleanly without untangling a polymorphic settlement table.
Customer return risk and parent RMA self-reference
Serial returners and split / re-opened RMAs are core fraud and ops signals; denormalizing lifetime_return_rate_pct + return_risk_score onto customer and carrying parent_return_request_key on return_request lets fraud and support teams trace related returns without an extra bridge table.
Tables in the E-commerce Returns & RMA model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (9)
customer
Grain: one row per customer account
Customer master with returns-behavior denormalizations.
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_id | varchar | BK |
| store_key | bigint | |
| full_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| shipping_address | varchar | PII |
| billing_address | varchar | PII |
| country | varchar | |
| registered_at | timestamp | |
| lifetime_orders_count | integer | |
| lifetime_return_count | integer | |
| lifetime_return_rate_pct | decimal | |
| return_risk_score | decimal | |
| is_blocked_from_returns | boolean |
sales_order
Grain: one row per sales order header
Originating sales order header referenced by return requests.
| Column | Type | Keys |
|---|---|---|
| sales_order_key | bigint | PK |
| sales_order_id | varchar | BK |
| customer_key | bigint | |
| store_key | bigint | |
| order_date | date | |
| order_subtotal | decimal | |
| shipping_cost | decimal | |
| tax_amount | decimal | |
| order_total | decimal | |
| currency | varchar | |
| sales_channel | varchar | |
| payment_method | varchar |
sales_order_line
Grain: one row per sales order line item
Sales order line items — the grain that return lines reference.
| Column | Type | Keys |
|---|---|---|
| sales_order_line_key | bigint | PK |
| order_line_id | varchar | BK |
| sales_order_key | bigint | |
| product_key | bigint | |
| quantity | decimal | |
| unit_price | decimal | |
| line_subtotal | decimal | |
| discount_amount | decimal | |
| line_total | decimal | |
| currency | varchar |
return_request
Grain: one row per RMA / return request header
RMA header — mirrors the originating sales order header.
| Column | Type | Keys |
|---|---|---|
| return_request_key | bigint | PK |
| return_request_id | varchar | BK |
| sales_order_key | bigint | |
| customer_key | bigint | |
| store_key | bigint | |
| return_method_key | bigint | |
| return_status_key | bigint | |
| requested_at | timestamp | |
| approved_at | timestamp | |
| expires_at | timestamp | |
| return_window_days | integer | |
| total_refund_estimate | decimal | |
| restocking_fee_amount | decimal | |
| currency | varchar | |
| return_origin_address | varchar | PII |
| channel_initiated | varchar | |
| fraud_score | decimal | |
| parent_return_request_key | bigint |
return_line
Grain: one row per RMA line item
RMA line item — mirrors sales_order_line at the return grain.
| Column | Type | Keys |
|---|---|---|
| return_line_key | bigint | PK |
| return_line_id | varchar | BK |
| return_request_key | bigint | |
| sales_order_line_key | bigint | |
| product_key | bigint | |
| return_reason_key | bigint | |
| requested_quantity | decimal | |
| received_quantity | decimal | |
| item_refund_amount | decimal | |
| restocking_fee_amount | decimal | |
| currency | varchar | |
| line_status | varchar | |
| evidence_photos_count | integer |
return_shipment
Grain: one row per return shipment / label
Carrier-tracked return shipment for in-transit visibility.
| Column | Type | Keys |
|---|---|---|
| return_shipment_key | bigint | PK |
| return_shipment_id | varchar | BK |
| return_request_key | bigint | |
| carrier_key | bigint | |
| label_issued_at | timestamp | |
| label_cost | decimal | |
| tracking_number | varchar | |
| picked_up_at | timestamp | |
| received_at | timestamp | |
| shipping_status | varchar | |
| currency | varchar |
inspection_grade
Grain: one row per inspection grading event on a return line
Reverse-logistics grading and disposition assignment.
| Column | Type | Keys |
|---|---|---|
| inspection_grade_key | bigint | PK |
| inspection_grade_id | varchar | BK |
| return_line_key | bigint | |
| disposition_key | bigint | |
| inspected_at | timestamp | |
| grade | varchar | |
| condition_notes | varchar | |
| restocked_quantity | decimal | |
| scrapped_quantity | decimal | |
| sent_to_vendor_quantity | decimal | |
| evidence_photos_count | integer | |
| inspector_name | varchar | PII |
refund_event
Grain: one row per refund event posted against an RMA
Refund settlement event — separate from exchanges to keep revenue/COGS impact queryable.
| Column | Type | Keys |
|---|---|---|
| refund_event_key | bigint | PK |
| refund_event_id | varchar | BK |
| return_request_key | bigint | |
| customer_key | bigint | |
| refund_method_key | bigint | |
| sales_order_key | bigint | |
| refund_amount | decimal | |
| refund_currency | varchar | |
| processed_at | timestamp | |
| payment_processor_reference | varchar | |
| refund_status | varchar | |
| refund_breakdown_items | decimal | |
| refund_breakdown_shipping | decimal | |
| refund_breakdown_tax | decimal |
exchange_order
Grain: one row per exchange order issued in lieu of refund
Exchange settlement — separated from refund_event for clean recommerce attribution.
| Column | Type | Keys |
|---|---|---|
| exchange_order_key | bigint | PK |
| exchange_order_id | varchar | BK |
| return_request_key | bigint | |
| customer_key | bigint | |
| product_key | bigint | |
| exchange_quantity | decimal | |
| new_sales_order_id | varchar | |
| exchange_status | varchar | |
| currency | varchar | |
| price_difference | decimal |
Lookup tables (8)
store
Grain: one row per retail store / brand storefront
Storefront / banner reference table.
| Column | Type | Keys |
|---|---|---|
| store_key | bigint | PK |
| store_id | varchar | BK |
| store_name | varchar | |
| brand | varchar | |
| country | varchar | |
| currency | varchar | |
| is_active | boolean |
product
Grain: one row per sellable product / SKU
Product / SKU reference table.
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| product_id | varchar | BK |
| product_name | varchar | |
| category | varchar | |
| brand | varchar | |
| is_perishable | boolean | |
| is_high_value | boolean | |
| is_oversized | boolean | |
| base_price | decimal | |
| currency | varchar |
carrier
Grain: one row per parcel carrier
Parcel carrier reference table for return shipments.
| Column | Type | Keys |
|---|---|---|
| carrier_key | bigint | PK |
| carrier_id | varchar | BK |
| carrier_name | varchar | |
| country | varchar | |
| services_offered | varchar |
return_reason
Grain: one row per return reason code
Return reason catalog driving downstream root-cause analysis.
| Column | Type | Keys |
|---|---|---|
| return_reason_key | bigint | PK |
| return_reason_id | varchar | BK |
| description | varchar | |
| reason_category | varchar | |
| is_customer_fault | boolean | |
| requires_evidence | boolean |
return_method
Grain: one row per return delivery method
Channel through which the customer returns goods.
| Column | Type | Keys |
|---|---|---|
| return_method_key | bigint | PK |
| return_method_id | varchar | BK |
| description | varchar | |
| average_cost | decimal | |
| currency | varchar | |
| average_lead_days | decimal |
disposition
Grain: one row per disposition outcome
Reverse-logistics disposition outcomes after grading.
| Column | Type | Keys |
|---|---|---|
| disposition_key | bigint | PK |
| disposition_id | varchar | BK |
| description | varchar | |
| is_sellable_again | boolean | |
| recovery_pct | decimal | |
| channel | varchar |
refund_method
Grain: one row per refund settlement method
Settlement method for refund events.
| Column | Type | Keys |
|---|---|---|
| refund_method_key | bigint | PK |
| refund_method_id | varchar | BK |
| description | varchar | |
| processing_days | decimal | |
| is_electronic | boolean |
return_status
Grain: one row per return lifecycle status
Return request lifecycle status catalog.
| Column | Type | Keys |
|---|---|---|
| return_status_key | bigint | PK |
| return_status_id | varchar | BK |
| description | varchar | |
| is_terminal | boolean | |
| is_open | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- customer.store_key → store (many-to-one)
- sales_order.customer_key → customer (many-to-one)
- sales_order.store_key → store (many-to-one)
- sales_order_line.sales_order_key → sales_order (many-to-one)
- sales_order_line.product_key → product (many-to-one)
- return_request.sales_order_key → sales_order (many-to-one)
- return_request.customer_key → customer (many-to-one)
- return_request.store_key → store (many-to-one)
- return_request.return_method_key → return_method (many-to-one)
- return_request.return_status_key → return_status (many-to-one)
- return_line.return_request_key → return_request (many-to-one)
- return_line.sales_order_line_key → sales_order_line (many-to-one)
- return_line.product_key → product (many-to-one)
- return_line.return_reason_key → return_reason (many-to-one)
- return_shipment.return_request_key → return_request (many-to-one)
- return_shipment.carrier_key → carrier (many-to-one)
- inspection_grade.return_line_key → return_line (many-to-one)
- inspection_grade.disposition_key → disposition (many-to-one)
- refund_event.return_request_key → return_request (many-to-one)
- refund_event.customer_key → customer (many-to-one)
- refund_event.refund_method_key → refund_method (many-to-one)
- refund_event.sales_order_key → sales_order (many-to-one)
- exchange_order.return_request_key → return_request (many-to-one)
- exchange_order.customer_key → customer (many-to-one)
- exchange_order.product_key → product (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.