E-commerce (polyglot)
File: 02-ecommerce.xdbml · Target: Oracle + MongoDB hybrid
A polyglot e-commerce schema combining Oracle relational system-of-record with MongoDB document storage. Demonstrates named types (including the object-form Address Type with a nested location sub-object for bounded geographic coordinates), nested arrays of objects, polymorphism with discriminator, BSON scalar types, cross-container relationships with explicit cardinality, and array traversal in foreign-key paths.
Source
xdbml
xdbml: 0.3
Project ecommerce {
targets: [Oracle, MongoDB]
Note: '''
A polyglot e-commerce schema combining an Oracle relational system
of record (customers, products, orders) with a MongoDB document
store for shopping carts and order events. Demonstrates nested
types, polymorphism, BSON scalar types, cross-container
relationships with explicit cardinality, and array traversal in
foreign-key paths.
Two distinct storage decisions drive the architecture:
- Customer and product master data live in Oracle for transactional
consistency and reporting access from existing BI tools.
- Cart state and order event streams live in MongoDB for flexible
schema evolution and document-shaped access patterns.
'''
}
// ============================================================
// Reusable types
// ============================================================
Type Address {
Note: '''
Postal address used across customers, orders, and shipping records.
Uses BSON-native string type so the same Type lowers cleanly to both
Oracle VARCHAR2(n) and MongoDB string with maxLength constraint.
Includes an optional `location` object for geographic centroid -- the
nested object form demonstrates that Named Types can carry richly
structured payloads, not just scalar shapes.
'''
street string [not null, maxLength: 200]
city string [not null, maxLength: 100]
state string [maxLength: 100, note: 'State, province, or region']
postal_code string [maxLength: 20, note: 'ZIP, postcode, etc., format varies by country']
country string [not null,
minLength: 2,
maxLength: 2,
default: 'US',
note: 'ISO 3166-1 alpha-2 country code']
location object {
Note: 'Optional geographic centroid for the address; populated by the geocoding service after order entry.'
latitude Decimal128 [minimum: -90,
maximum: 90,
note: 'Decimal degrees on the WGS 84 reference ellipsoid; positive = north of equator.']
longitude Decimal128 [minimum: -180,
maximum: 180,
note: 'Decimal degrees; positive = east of prime meridian.']
}
}
Type MonetaryAmount {
Note: '''
Decimal monetary value with ISO 4217 currency code. Used throughout
commerce flows in both relational and document contexts.
Decimal128 is the BSON-native fixed-format decimal; in Oracle/PostgreSQL
Containers, it lowers to NUMBER(38,4) / numeric(34,4) per spec §21.3.
'''
amount Decimal128 [not null,
minimum: 0,
note: 'Amount in major currency units (e.g., dollars not cents)']
currency string [not null,
pattern: '^[A-Z]{3}$',
minLength: 3,
maxLength: 3,
default: 'USD',
note: 'ISO 4217 three-letter currency code']
}
// ============================================================
// Containers
// ============================================================
Container core [type: schema, target: Oracle] {
Note: 'Oracle schema holding customer and product master data. Source of truth for entities that need transactional integrity.'
Entity customers {
Note: 'Registered customer accounts. One row per natural person or business entity.'
id int [pk,
increment,
note: 'Surrogate primary key; never reused even after account deletion']
email varchar [unique,
not null,
pattern: '^[^@]+@[^@]+\.[^@]+$',
tags: ['pii', 'contact', 'gdpr-subject'],
note: 'Login identifier and primary contact channel']
display_name varchar [not null,
maxLength: 120,
synonyms: ['customer name', 'full name'],
note: 'Public-facing name shown on orders and receipts']
primary_address Address [note: 'Default shipping and billing address; can be overridden per order']
loyalty_tier varchar [enum: ['bronze', 'silver', 'gold', 'platinum'],
default: 'bronze',
business_term: 'Customer Loyalty Tier',
note: 'Determines discount eligibility and shipping benefits']
created_at timestamp [not null,
default: `SYSTIMESTAMP`,
granularity: second,
note: 'Account creation timestamp; immutable after insert']
}
Entity products {
Note: 'Sellable products. SKU is the natural key used across systems.'
sku varchar [pk,
pattern: '^[A-Z]{3}-[0-9]{6}$',
maxLength: 12,
note: 'Stock-keeping unit; format: three-letter category prefix and six-digit sequence']
name varchar [not null,
maxLength: 200,
synonyms: ['product name', 'item name', 'product title'],
note: 'Customer-facing product title']
description varchar [maxLength: 5000, note: 'Long-form product description; markdown allowed']
price MonetaryAmount [note: 'Current list price; promotional prices applied at checkout time']
inventory int [not null,
minimum: 0,
default: 0,
note: 'Units in stock; decremented on confirmed order, not on cart addition']
is_active boolean [not null,
default: true,
note: 'False indicates discontinued product; kept for historical order references']
}
}
Container orders_store [type: database, target: MongoDB] {
Note: '''
MongoDB database holding cart state and order events. Chosen for
document-shape access patterns and flexible schema evolution as new
payment methods, fulfillment options, and order types are added.
'''
Collection orders {
Note: '''
Confirmed customer orders. Documents are write-once after creation --
status changes and fulfillment events are appended as separate
records in the events container rather than mutated here.
'''
_id objectId [pk, note: 'MongoDB-generated 12-byte unique identifier']
customer_id int32 [not null, note: 'References core.customers.id; the buyer']
placed_at Date [not null,
granularity: second,
note: 'Order placement timestamp; UTC']
total MonetaryAmount [note: 'Computed at placement; includes tax and shipping but not promotions applied later']
shipping_address Address [note: 'Frozen at placement time even if customer subsequently updates their primary address']
line_items array [
line_item object {
Note: 'A single line item within an order.'
sku string [not null,
pattern: '^[A-Z]{3}-[0-9]{6}$',
note: 'Snapshot of product SKU at placement time']
product_name string [not null,
note: 'Snapshot of product name; preserves historical record if product is renamed']
quantity int32 [not null,
minimum: 1,
note: 'Number of units of this SKU in this order']
unit_price MonetaryAmount [note: 'Snapshot of unit price at placement time; immune to subsequent price changes']
}
] [minItems: 1, note: 'An order must contain at least one line item']
payment_method oneOf {
card object {
Note: 'Credit or debit card payment'
last4 string [not null,
maxLength: 4,
pattern: '^[0-9]{4}$',
note: 'Last four digits of card number; full PAN never stored, PCI-compliant tokenized reference elsewhere']
brand string [enum: ['visa', 'mastercard', 'amex', 'discover'], note: 'Card network']
exp_month int32 [minimum: 1, maximum: 12]
exp_year int32 [minimum: 2024, note: 'Two-digit year would be ambiguous; use four']
}
bank object {
Note: 'Direct bank transfer (ACH in the US, SEPA in Europe)'
iban string [not null, maxLength: 34, note: 'International Bank Account Number where applicable']
bic string [maxLength: 11, note: 'Bank Identifier Code; optional within SEPA zone']
}
wallet object {
Note: 'Digital wallet payment'
provider string [enum: ['paypal', 'apple_pay', 'google_pay', 'venmo'], note: 'Wallet service used']
account string [not null, note: 'Tokenized wallet account reference']
}
} [discriminator: method_kind, note: 'One of three payment shapes; method_kind field carries the discriminator value']
notes string [maxLength: 500, note: 'Optional customer notes at checkout, e.g. "leave at back door"']
}
}
// ============================================================
// Cross-container relationships
// ============================================================
Ref: orders_store.orders.customer_id > core.customers.id [source: '1..*', target: '1..1']
Ref: orders_store.orders.line_items.[*].sku > core.products.sku [source: '1..*', target: '1..1']