Skip to main content

E-commerce

A small storefront that exercises every relationship form DBML offers: inline refs, short form, long form, all four cardinalities (<, >, -, <>), optional sides (?), composite foreign keys, named refs and referential actions.

Covers: Project; refs in inline, short and long form; all cardinalities < > - <>; optional sides >? and ?<; self reference; composite PK via index; composite FKs t.(a, b) > u.(a, b); named refs; delete / update actions (cascade, restrict, set null, no action); junction table and direct many-to-many

02-ecommerce.dbml
02-ecommerce.dbmlpublic.countries.code < public.merchants.country_codepublic.users.id - public.user_profiles.user_idpublic.categories.id ?< public.categories.parent_idpublic.merchants.admin_id > public.admins.idfk_orders_user: public.orders.user_id > public.users.id delete: restrict update: cascadepublic.users.country_code > public.countries.code delete: set nullpublic.orders.coupon_id >? public.coupons.id delete: set nullorder_lines: public.order_items.order_id > public.orders.id delete: cascadepublic.order_items.product_id > public.products.id delete: restrict update: no actionorder_payments: public.orders.id < public.payments.order_id delete: cascadepublic.products.(merchant_id, merchant_country) > public.merchants.(id, country_code) delete: cascademerchant_period_owner: public.merchant_periods.(merchant_id, country_code) > public.merchants.(id, country_code)product_tags: public.products.id <> public.tags.idpublic.categories.id ?< public.products.category_idcountriescountriescode char(2) · primary keycodechar(2)name varchar(100) · not nullnameNNvarchar(100)continent_name varchar(50)continent_namevarchar(50)merchantsmerchantsid integer · incrementidintegercountry_code char(2) · foreign keycountry_codechar(2)merchant_name varchar(120) · not nullmerchant_nameNNvarchar(120)admin_id integer · foreign keyadmin_idintegercreated_at timestamp · default: now()created_attimestampmerchant_periodsmerchant_periodsid integer · primary key · incrementidintegermerchant_id integer · foreign key · not nullmerchant_idNNintegercountry_code char(2) · foreign key · not nullcountry_codeNNchar(2)start_date date · not nullstart_dateNNdateend_date dateend_datedateusersusersid integer · primary key · incrementidintegerfull_name varchar(200)full_namevarchar(200)email varchar(255) · unique · not nullemailUNNvarchar(255)country_code char(2) · foreign keycountry_codechar(2)created_at timestamp · default: now()created_attimestampuser_profilesuser_profilesuser_id integer · primary key · foreign keyuser_idintegeravatar_url varchar(500)avatar_urlvarchar(500)bio textbiotextcategoriescategoriesid integer · primary key · incrementidintegerparent_id integer · foreign key Self reference; null for root categoriesparent_idintegername varchar(100) · not nullnameNNvarchar(100)productsproductsid integer · primary key · incrementidintegername varchar(200) · not nullnameNNvarchar(200)merchant_id integer · foreign key · not nullmerchant_idNNintegermerchant_country char(2) · foreign key · not nullmerchant_countryNNchar(2)category_id integer · foreign keycategory_idintegerprice decimal(10,2) · not null · default: 0priceNNdecimal(10,2)status varchar(20) · default: draftstatusvarchar(20)created_at timestamp · default: now()created_attimestamptagstagsid integer · primary key · incrementidintegerlabel varchar(50) · unique · not nulllabelUNNvarchar(50)ordersordersid integer · primary key · incrementidintegeruser_id integer · foreign key · not nulluser_idNNintegerstatus varchar(20) · not null · default: pendingstatusNNvarchar(20)coupon_id integer · foreign keycoupon_idintegercreated_at timestamp · default: now()created_attimestamporder_itemsorder_itemsorder_id integer · foreign keyorder_idintegerproduct_id integer · foreign keyproduct_idintegerquantity integer · not null · default: 1quantityNNintegerunit_price decimal(10,2) · not nullunit_priceNNdecimal(10,2)couponscouponsid integer · primary key · incrementidintegercode varchar(32) · unique · not nullcodeUNNvarchar(32)percent_off integerpercent_offintegerpaymentspaymentsid integer · primary key · incrementidintegerorder_id integer · foreign key · not nullorder_idNNintegeramount decimal(10,2) · not nullamountNNdecimal(10,2)paid_at timestamppaid_attimestampadminsadminsid integer · primary key · incrementidintegerlogin varchar(64) · unique · not nullloginUNNvarchar(64)

Source​

examples/02-ecommerce.dbml
examples/02-ecommerce.dbml
// A small storefront that exercises every relationship form DBML offers:
// inline refs, short form, long form, all four cardinalities (<, >, -, <>),
// optional sides (?), composite foreign keys, named refs and referential actions.

Project storefront {
database_type: 'PostgreSQL'
Note: 'Storefront schema used to demonstrate relationships.'
}

Table countries {
code char(2) [pk]
name varchar(100) [not null]
continent_name varchar(50)
}

Table merchants {
id integer [increment]
country_code char(2) [ref: > countries.code]
merchant_name varchar(120) [not null]
admin_id integer
created_at timestamp [default: `now()`]

indexes {
(id, country_code) [pk]
}
}

// Composite foreign key target: merchants.(id, country_code)
Table merchant_periods {
id integer [pk, increment]
merchant_id integer [not null]
country_code char(2) [not null]
start_date date [not null]
end_date date
}

Table users {
id integer [pk, increment]
full_name varchar(200)
email varchar(255) [not null, unique]
country_code char(2)
created_at timestamp [default: `now()`]
}

// One-to-one: every user has at most one profile; user_profiles.user_id is the FK.
Table user_profiles {
user_id integer [pk, ref: - users.id]
avatar_url varchar(500)
bio text
}

Table categories {
id integer [pk, increment]
parent_id integer [ref: >? categories.id, note: 'Self reference; null for root categories']
name varchar(100) [not null]
}

Table products {
id integer [pk, increment]
name varchar(200) [not null]
merchant_id integer [not null]
merchant_country char(2) [not null]
category_id integer
price "decimal(10,2)" [not null, default: 0]
status varchar(20) [default: 'draft']
created_at timestamp [default: `now()`]
}

// Many-to-many expressed directly with <>.
Table tags {
id integer [pk, increment]
label varchar(50) [not null, unique]
}

Table orders {
id integer [pk, increment]
user_id integer [not null]
status varchar(20) [not null, default: 'pending']
coupon_id integer
created_at timestamp [default: `now()`]
}

// Many-to-many expressed as a junction table with two many-to-one refs.
Table order_items {
order_id integer
product_id integer
quantity integer [not null, default: 1]
unit_price "decimal(10,2)" [not null]

indexes {
(order_id, product_id) [pk]
}
}

Table coupons {
id integer [pk, increment]
code varchar(32) [not null, unique]
percent_off integer
}

Table payments {
id integer [pk, increment]
order_id integer [not null]
amount "decimal(10,2)" [not null]
paid_at timestamp
}

// One-to-many written from the "one" side, inline.
Table admins {
id integer [pk, increment, ref: < merchants.admin_id]
login varchar(64) [not null, unique]
}

// Short form, many-to-one, with referential actions.
Ref fk_orders_user: orders.user_id > users.id [delete: restrict, update: cascade]

// Short form, one-to-many (written from the parent side).
Ref: users.country_code > countries.code [delete: set null]

// Optional side: an order may have no coupon.
Ref: orders.coupon_id >? coupons.id [delete: set null]

// Long form with several refs in one block and per-ref settings.
Ref order_lines {
order_items.order_id > orders.id [delete: cascade]
}

Ref {
order_items.product_id > products.id [delete: restrict, update: no action]
}

// Long form, one-to-many with explicit name.
Ref order_payments {
orders.id < payments.order_id [delete: cascade]
}

// Composite foreign keys.
Ref: products.(merchant_id, merchant_country) > merchants.(id, country_code) [delete: cascade]
Ref merchant_period_owner: merchant_periods.(merchant_id, country_code) > merchants.(id, country_code)

// Many-to-many.
Ref product_tags: products.id <> tags.id

// Optional side on the "one" end: a product may be uncategorised.
Ref: categories.id ?< products.category_id