ERP Order Management data model
Normalized (3NF) OLTP model of an ERP order-management module modeled on SAP SD / Oracle EBS / NetSuite structures. Each business entity is its own table with a surrogate key and natural business key, connected with foreign keys from child detail tables toward customer/order/product roots. Customer contact and tax identifiers are PII-masked under GDPR.
Operational order-to-cash data for an ERP: sold-to customers and their ship-to/bill-to addresses, a product master priced through versioned price lists, sales orders and order lines, outbound shipments, AR invoices and invoice lines, and applied customer payments — anchored to org-unit, currency, and unit-of-measure reference tables. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 14
- tables
- 119
- columns
- 29
- relationships
- 6
- 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
Order-to-cash decomposition
Splitting sales order, shipment, invoice, and payment into separate documents mirrors the standard ERP O2C process flow, where each step is a distinct posted document with its own lifecycle and number range.
Versioned price lists rather than a price on the product
Net unit prices vary by sales org, currency, customer group, and validity window; a price-list header with line items keeps the product master clean and supports time-bounded pricing.
Normalized (3NF) for transactional integrity
Order management is write-heavy OLTP requiring referential integrity and minimal update anomalies, so a fully normalized relational model on Postgres is preferred over a dimensional star.
PII classification on customer identifiers
Email, phone, postal address, and tax ID are personal data under GDPR and are tagged/masked so the governance report flags them and downstream extracts redact them by default.
Tables in the ERP Order Management 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 (12)
org_unit
Grain: one row per selling organization / company code
| Column | Type | Keys |
|---|---|---|
| org_unit_key | bigint | PK |
| org_code | varchar | BK |
| org_name | varchar | |
| functional_currency_key | bigint | |
| country | varchar | |
| is_active | boolean |
customer
Grain: one row per sold-to customer master record
Sold-to party master.
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| org_unit_key | bigint | |
| customer_number | varchar | BK |
| legal_name | varchar | |
| contact_email | varchar | PII |
| contact_phone | varchar | PII |
| tax_id | varchar | PII |
| credit_limit | decimal | |
| default_currency_key | bigint | |
| payment_terms | varchar | |
| customer_group | varchar | |
| is_active | boolean |
address
Grain: one row per customer address (ship-to / bill-to)
| Column | Type | Keys |
|---|---|---|
| address_key | bigint | PK |
| customer_key | bigint | |
| address_type | varchar | |
| line1 | varchar | PII |
| line2 | varchar | PII |
| city | varchar | |
| region | varchar | |
| postal_code | varchar | PII |
| country | varchar | |
| is_default | boolean |
product
Grain: one row per material / product master record
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| sku | varchar | BK |
| product_name | varchar | |
| product_category | varchar | |
| base_uom_key | bigint | |
| weight_kg | decimal | |
| is_serialized | boolean | |
| is_active | boolean |
price_list
Grain: one row per versioned price list header
| Column | Type | Keys |
|---|---|---|
| price_list_key | bigint | PK |
| org_unit_key | bigint | |
| currency_key | bigint | |
| price_list_code | varchar | BK |
| description | varchar | |
| valid_from | date | |
| valid_to | date | |
| is_active | boolean |
price_list_item
Grain: one row per product price on a price list
| Column | Type | Keys |
|---|---|---|
| price_list_item_key | bigint | PK |
| price_list_key | bigint | |
| product_key | bigint | |
| unit_price | decimal | |
| min_quantity | decimal |
sales_order
Grain: one row per sales order header
Sales document header.
| Column | Type | Keys |
|---|---|---|
| sales_order_key | bigint | PK |
| org_unit_key | bigint | |
| customer_key | bigint | |
| ship_to_address_key | bigint | |
| bill_to_address_key | bigint | |
| currency_key | bigint | |
| order_number | varchar | BK |
| order_date | date | |
| requested_delivery_date | date | |
| order_status | varchar | |
| order_total_amount | decimal | |
| po_reference | varchar |
order_line
Grain: one row per sales-order line item
| Column | Type | Keys |
|---|---|---|
| order_line_key | bigint | PK |
| sales_order_key | bigint | |
| product_key | bigint | |
| uom_key | bigint | |
| line_number | integer | |
| ordered_quantity | decimal | |
| unit_price | decimal | |
| discount_amount | decimal | |
| net_amount | decimal | |
| requested_date | date | |
| line_status | varchar |
shipment
Grain: one row per outbound shipment / delivery document
| Column | Type | Keys |
|---|---|---|
| shipment_key | bigint | PK |
| sales_order_key | bigint | |
| ship_from_org_key | bigint | |
| ship_to_address_key | bigint | |
| delivery_number | varchar | BK |
| carrier | varchar | |
| tracking_number | varchar | |
| shipped_date | date | |
| delivered_date | date | |
| shipment_status | varchar |
invoice
Grain: one row per AR invoice / billing document
Accounts-receivable billing document.
| Column | Type | Keys |
|---|---|---|
| invoice_key | bigint | PK |
| sales_order_key | bigint | |
| customer_key | bigint | |
| currency_key | bigint | |
| invoice_number | varchar | BK |
| invoice_date | date | |
| due_date | date | |
| net_amount | decimal | |
| tax_amount | decimal | |
| gross_amount | decimal | |
| invoice_status | varchar |
invoice_line
Grain: one row per invoice line item
| Column | Type | Keys |
|---|---|---|
| invoice_line_key | bigint | PK |
| invoice_key | bigint | |
| order_line_key | bigint | |
| product_key | bigint | |
| billed_quantity | decimal | |
| unit_price | decimal | |
| net_amount | decimal | |
| tax_amount | decimal | |
| tax_code | varchar |
payment
Grain: one row per customer payment applied to an invoice
| Column | Type | Keys |
|---|---|---|
| payment_key | bigint | PK |
| invoice_key | bigint | |
| customer_key | bigint | |
| currency_key | bigint | |
| payment_reference | varchar | BK |
| payment_method | varchar | |
| amount | decimal | |
| received_at | timestamp | |
| payment_status | varchar |
Lookup tables (2)
currency
Grain: one row per ISO 4217 currency
| Column | Type | Keys |
|---|---|---|
| currency_key | bigint | PK |
| currency_code | varchar | BK |
| currency_name | varchar | |
| minor_unit | smallint |
uom
Grain: one row per unit of measure
| Column | Type | Keys |
|---|---|---|
| uom_key | bigint | PK |
| uom_code | varchar | BK |
| uom_name | varchar | |
| dimension | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- org_unit.functional_currency_key → currency (many-to-one)
- customer.org_unit_key → org_unit (many-to-one)
- customer.default_currency_key → currency (many-to-one)
- address.customer_key → customer (many-to-one)
- product.base_uom_key → uom (many-to-one)
- price_list.org_unit_key → org_unit (many-to-one)
- price_list.currency_key → currency (many-to-one)
- price_list_item.price_list_key → price_list (many-to-one)
- price_list_item.product_key → product (many-to-one)
- sales_order.org_unit_key → org_unit (many-to-one)
- sales_order.customer_key → customer (many-to-one)
- sales_order.ship_to_address_key → address (many-to-one)
- sales_order.bill_to_address_key → address (many-to-one)
- sales_order.currency_key → currency (many-to-one)
- order_line.sales_order_key → sales_order (many-to-one)
- order_line.product_key → product (many-to-one)
- order_line.uom_key → uom (many-to-one)
- shipment.sales_order_key → sales_order (many-to-one)
- shipment.ship_from_org_key → org_unit (many-to-one)
- shipment.ship_to_address_key → address (many-to-one)
- invoice.sales_order_key → sales_order (many-to-one)
- invoice.customer_key → customer (many-to-one)
- invoice.currency_key → currency (many-to-one)
- invoice_line.invoice_key → invoice (many-to-one)
- invoice_line.order_line_key → order_line (many-to-one)
- invoice_line.product_key → product (many-to-one)
- payment.invoice_key → invoice (many-to-one)
- payment.customer_key → customer (many-to-one)
- payment.currency_key → currency (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.