Marketplace core entities · Data Modeling scenario

Preparing your workspaceloading the brief, studio & canvas…

Loadingone moment…

Preparing your workspaceloading the brief, studio & canvas…

buyersellerlistingorder_item

SCENARIO

You have joined Bricklane — a marketplace where independent sellers list handmade goods and buyers order them. You are designing the database the app itself writes to when someone lists an item, checks out, or pays. Two things about how Bricklane works: one cart can hold items from several sellers, and sellers keep editing their listings — price, title — long after orders have shipped.

your ERD must answer

01What is each seller offering right now — and at what price?
02What exactly did a buyer order, when, and what were they charged at that moment?
03A buyer pays once for a multi-seller cart — who is owed what?

out of scopeAnalytics & warehouse design · shipping · reviews · promotions

01requirements

Is this the app’s own database, or a reporting one?
e.g.
The app’s own — OLTP. It is written to live, at checkout. The analytics warehouse (OLAP) is a separate job for later.
What must it answer on day one?
e.g.
What is on sale, what was ordered, and who gets paid.
Does one word mean two different things?
e.g.
“Price” is two things: what a seller is asking today, and what a buyer actually paid last week.
What must stay true when things change?
e.g.
A seller can reprice a listing tomorrow. Yesterday’s order still has to show what was charged.
What are you not building in v1?
e.g.
Analytics, shipping, reviews, promotions — said out loud, not left ambiguous.

New to this?Applied Data Modeling · Step 1: Clarify

model answer — edit to make it yours✓ captured

**What it is for.** The app's own database (**OLTP**), written live at checkout. Analytics comes later, separately.

**What it must answer:**
1) **Catalog** — what is each seller selling now, and for how much?
2) **Orders** — what did a buyer buy, when, and what were they charged?
3) **Payouts** — one payment, several sellers: who is owed what?

**"Price" is two facts.** A listing's *asking price* changes whenever the seller likes. An order line's *charged price* must never change. Two columns, not one. The same is true of *title*.

**What changes later.** Sellers reprice after orders ship, and one cart can hold several sellers' items, so payouts are per line, not per order.

**Assumptions I say out loud.** A listing belongs to one shop for life. Buyers and sellers are separate accounts in v1 — if one person can be both, a shared users table with roles is the v2 change. What a buyer is charged equals the sum of their lines, because shipping, tax and discounts are out of scope.

**Out of scope:** analytics, shipping, reviews, promotions.

Markdown: **bold** · *italic* · # heading · - bullet

surface:oltp ✓catalog ✓orders ✓payouts ✓reprice ✓out of scope ✓

02entities

What does the business already have names for?
e.g.
Buyer, seller, listing, order, payment.
Is it a thing, or a detail about a thing?
e.g.
A listing you can describe and count. “Paid” is only a status on a payment, so it stays a column.
For one order, how many listings? And the other way round?
e.g.
An order covers many listings. A listing sells in many orders. Many on both sides.
When both sides are many, where do the shared facts live?
e.g.
2 mugs and 1 scarf in one order. The 2 fits neither the order nor the mug listing. It only means something for that mug in that order.
Does it have a life of its own?
e.g.
A payment goes pending, paid or failed, retried. Own story, own table.

New to this?Entities, Relationships & Cardinality · Many-to-Many & Bridge Tables

model answer — edit to make it yours✓ captured

Markdown: **bold** · *italic* · # heading · - bullet

surface:buyer ✓seller ✓listing ✓order_item ✓payment ✓status ✓

03grain

Can you finish “one row per ___” for every table?
e.g.
One row per checkout for orders. One row per item inside one order for order_items.
Does the sentence need the word “and”?
e.g.
“One checkout and what was in it” is two tables wearing one name. Split it.
Could the same real thing appear twice?
e.g.
Two rows for the same mug in the same order means the grain is not what you said it was.

New to this?Grain: What One Row Means · Declaring The Grain

model answer — edit to make it yours✓ captured

Markdown: **bold** · *italic* · # heading · - bullet

surface:buyers ✓sellers ✓listings ✓orders ✓order_items ✓payments ✓

04keys & identity

Could this value ever change?
e.g.
Emails change. Shop names change. Anything a user can edit cannot be the identity.
Is it unique, or is it the identity?
e.g.
A buyer email must be unique. That is a rule the database enforces, not the column you join on.
Which table needs two columns to name one row?
e.g.
order_items. One row is one item inside one order, so its key is that pair.

New to this?Keys & Identity · Natural vs Surrogate

model answer — edit to make it yours✓ captured

**Key and columns, per table**
- **buyers** — buyer_id (PK) · email (UNIQUE) · full_name · created_at
- **sellers** — seller_id (PK) · email (UNIQUE) · shop_name · created_at
- **listings** — listing_id (PK) · seller_id (FK, NOT NULL) · title · unit_price (asking) · status
- **orders** — order_id (PK) · buyer_id (FK, NOT NULL) · placed_at
- **order_items** — (order_id, listing_id) (PK, both FK) · qty · unit_price (charged) · title (as sold)
- **payments** — payment_id (PK) · order_id (FK, NOT NULL) · amount · status · attempted_at

**Why surrogate ids**
- An identity must never change, and anything a user can edit eventually does
- So email is UNIQUE on both buyers and sellers — a rule the database enforces, never the column you join on

**The composite is the grain**
- order_items has no id of its own: one row is one listing inside one order, so the pair is the key
- The key also states a policy: the same listing cannot appear twice in one order — a repeat raises qty
- If a line ever has to repeat (two gift wraps with different messages), give the line its own order_item_id and keep the pair as a plain index

**Money safety lives in keys too**
- At most one *paid* payment per order: a partial unique index on payments(order_id) WHERE status = 'paid'. Retrying a failed charge adds a row; capturing twice is refused
- The processor's charge reference is the natural idempotency key — I would store it UNIQUE the day we integrate one

Markdown: **bold** · *italic* · # heading · - bullet

surface:surrogate ✓unique ✓composite ✓never change ✓one paid per order ✓

05relationships

For one of these, how many of the other?
e.g.
One seller has many listings. One listing has one seller. That is 1:M.
Can the foreign key ever be empty?
e.g.
A listing without a seller is nonsense, so listings.seller_id is NOT NULL.
What real case would force a NULL?
e.g.
Guest checkout. An order with no registered buyer makes orders.buyer_id nullable.

New to this?Entities, Relationships & Cardinality · Participation

model answer — edit to make it yours✓ captured

**Every line: how many, which column carries it, and can it be empty.**

- **sellers 1:M listings** · listings.seller_id → sellers.seller_id · NOT NULL · *seller-1 owns listing-1 and listing-2; every listing names exactly one seller, for life*
- **buyers 1:M orders** · orders.buyer_id → buyers.buyer_id · NOT NULL · *buyer-1 has order-1 and order-7; guest checkout would make this column nullable*
- **orders 1:M order_items** · order_items.order_id → orders.order_id · NOT NULL · *order-1 has two lines: (order-1, listing-1) qty 2 and (order-1, listing-9) qty 1; a line cannot outlive its order*
- **listings 1:M order_items** · order_items.listing_id → listings.listing_id · NOT NULL, ON DELETE RESTRICT · *listing-1 appears in order-1 and order-4; delisting it must not delete those lines*
- **orders 1:M payments** · payments.order_id → orders.order_id · NOT NULL · *order-1 has payment-1 failed and payment-2 paid; both rows stay*

**Guest checkout changes participation, not cardinality.** One buyer still has many orders. The column just becomes optional.

**The parent side is optional everywhere.** A new seller with no listings, a buyer with no orders, an order not paid yet — all valid rows. It is the child side I make mandatory.

Markdown: **bold** · *italic* · # heading · - bullet

surface:1:M ✓not null ✓nullable ✓guest checkout ✓restrict ✓

06history & change

If a seller changes this, does a past order still tell the truth?
e.g.
The mug drops from £14 to £9. Mara’s order must still say £14.
Copy the value, or point at it?
e.g.
Point at the listing and the price moves with it. Copy it onto the order line and it stays put.
What happens when a seller removes a listing?
e.g.
Delisting must never erase order history. Mark it, do not delete it.

New to this?Temporal & Historical Modeling · Two Kinds Of Time

model answer — edit to make it yours✓ captured

**Copy at purchase time, never point**
- order_items.unit_price is the price charged, written when the order is placed
- order_items.title is the name the buyer saw — *"I ordered the blue one"* is settled by the line, not by today's listing
- listings.unit_price and listings.title keep moving; the order line does not

**Never delete a listing**
- listings.status becomes delisted — a soft delete — so order history survives
- order_items.listing_id is ON DELETE RESTRICT — the database refuses to break it even if someone tries

**Never edit a payment**
- A failed attempt stays failed; the retry is a new row. The history of a charge is the rows themselves

**What I do not copy, and why**
- seller_id stays on the listing, because a listing belongs to one shop for life. If listings could move between shops, seller_id would have to be copied onto the line too — otherwise old payouts would rewrite themselves

**Deferred to v2**
- Full revision history of every listing edit. Naming it as deferred is part of the answer.

Markdown: **bold** · *italic* · # heading · - bullet

surface:price ✓title ✓purchase time ✓delisted ✓restrict ✓

07defend it

Which tables do you join, in order?
e.g.
Payouts: order_items → listings → sellers, for orders with a paid payment.
Where does the price come from?
e.g.
From order_items, never from listings. Reading the live price rewrites the past.
What is still undecided?
e.g.
Are refunds negative payments, or their own table? Naming it beats guessing.

New to this?Applied Data Modeling · Interview Narration

model answer — edit to make it yours✓ captured

**All three trace, so the model passes its own brief.**
- **Catalog** — listings, filtered by seller and status = 'active'. One table, no joins.
- **Orders** — orders → order_items, reading unit_price, title and qty from the order line — never from listings.
- **Payouts** — order_items → listings → sellers, summed per seller, for orders that have a paid payment. Each seller is owed the lines that are theirs, not a share of the payment.

**One trap in the payout query.** payments is per attempt, so joining every payment onto the lines counts an order twice when a failed attempt sits next to the paid one. Filter to the paid attempt, or test that one exists — never sum through that join.

**Still open, and I would raise these myself**
- Refunds: negative payments, or their own table?
- Platform commission: sellers are owed lines minus a fee — out of scope today, one column on order_items tomorrow.
- Guest checkout in v1? It decides whether orders.buyer_id is nullable.
- Inventory: nothing here stops overselling; stock is out of scope for this pass.

Open questions named early are cheaper than wrong guesses found late.

Markdown: **bold** · *italic* · # heading · - bullet

surface:joins ✓paid attempt ✓refunds ✓commission ✓guest checkout ✓

Build starts here

The brief becomes the left drawer. The canvas stays the build surface.Study is done above. Build the tables here, hit review to validate, then use follow-ups to defend the model.

Practice studio

Ctrl/Cmd + C copyCtrl/Cmd + V pasteCtrl/Cmd + Z undoDelete remove

68%

You built the model in the Studio. Now defend it.Marketplace core entities

Design review

The reviewer reads your model.

Desktop only

This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.

Read the library instead Back to modules

Preparing your workspaceloading the brief, studio & canvas…

buyersellerlistingorder_item

SCENARIO

your ERD must answer

01What is each seller offering right now — and at what price?
02What exactly did a buyer order, when, and what were they charged at that moment?
03A buyer pays once for a multi-seller cart — who is owed what?

out of scopeAnalytics & warehouse design · shipping · reviews · promotions

01requirements

Is this the app’s own database, or a reporting one?
e.g.
The app’s own — OLTP. It is written to live, at checkout. The analytics warehouse (OLAP) is a separate job for later.
What must it answer on day one?
e.g.
What is on sale, what was ordered, and who gets paid.
Does one word mean two different things?
e.g.
“Price” is two things: what a seller is asking today, and what a buyer actually paid last week.
What must stay true when things change?
e.g.
A seller can reprice a listing tomorrow. Yesterday’s order still has to show what was charged.
What are you not building in v1?
e.g.
Analytics, shipping, reviews, promotions — said out loud, not left ambiguous.

New to this?Applied Data Modeling · Step 1: Clarify

model answer — edit to make it yours✓ captured

**What it is for.** The app's own database (**OLTP**), written live at checkout. Analytics comes later, separately.

**"Price" is two facts.** A listing's *asking price* changes whenever the seller likes. An order line's *charged price* must never change. Two columns, not one. The same is true of *title*.

**What changes later.** Sellers reprice after orders ship, and one cart can hold several sellers' items, so payouts are per line, not per order.

**Out of scope:** analytics, shipping, reviews, promotions.

Markdown: **bold** · *italic* · # heading · - bullet

surface:oltp ✓catalog ✓orders ✓payouts ✓reprice ✓out of scope ✓

02entities

What does the business already have names for?
e.g.
Buyer, seller, listing, order, payment.
Is it a thing, or a detail about a thing?
e.g.
A listing you can describe and count. “Paid” is only a status on a payment, so it stays a column.
For one order, how many listings? And the other way round?
e.g.
An order covers many listings. A listing sells in many orders. Many on both sides.
When both sides are many, where do the shared facts live?
e.g.
2 mugs and 1 scarf in one order. The 2 fits neither the order nor the mug listing. It only means something for that mug in that order.
Does it have a life of its own?
e.g.
A payment goes pending, paid or failed, retried. Own story, own table.

New to this?Entities, Relationships & Cardinality · Many-to-Many & Bridge Tables

model answer — edit to make it yours✓ captured

Markdown: **bold** · *italic* · # heading · - bullet

surface:buyer ✓seller ✓listing ✓order_item ✓payment ✓status ✓

03grain

Can you finish “one row per ___” for every table?
e.g.
One row per checkout for orders. One row per item inside one order for order_items.
Does the sentence need the word “and”?
e.g.
“One checkout and what was in it” is two tables wearing one name. Split it.
Could the same real thing appear twice?
e.g.
Two rows for the same mug in the same order means the grain is not what you said it was.

New to this?Grain: What One Row Means · Declaring The Grain

model answer — edit to make it yours✓ captured

Markdown: **bold** · *italic* · # heading · - bullet

surface:buyers ✓sellers ✓listings ✓orders ✓order_items ✓payments ✓

04keys & identity

Could this value ever change?
e.g.
Emails change. Shop names change. Anything a user can edit cannot be the identity.
Is it unique, or is it the identity?
e.g.
A buyer email must be unique. That is a rule the database enforces, not the column you join on.
Which table needs two columns to name one row?
e.g.
order_items. One row is one item inside one order, so its key is that pair.

New to this?Keys & Identity · Natural vs Surrogate

model answer — edit to make it yours✓ captured

Markdown: **bold** · *italic* · # heading · - bullet

surface:surrogate ✓unique ✓composite ✓never change ✓one paid per order ✓

05relationships

For one of these, how many of the other?
e.g.
One seller has many listings. One listing has one seller. That is 1:M.
Can the foreign key ever be empty?
e.g.
A listing without a seller is nonsense, so listings.seller_id is NOT NULL.
What real case would force a NULL?
e.g.
Guest checkout. An order with no registered buyer makes orders.buyer_id nullable.

New to this?Entities, Relationships & Cardinality · Participation

model answer — edit to make it yours✓ captured

**Every line: how many, which column carries it, and can it be empty.**

**Guest checkout changes participation, not cardinality.** One buyer still has many orders. The column just becomes optional.

**The parent side is optional everywhere.** A new seller with no listings, a buyer with no orders, an order not paid yet — all valid rows. It is the child side I make mandatory.

Markdown: **bold** · *italic* · # heading · - bullet

surface:1:M ✓not null ✓nullable ✓guest checkout ✓restrict ✓

06history & change

If a seller changes this, does a past order still tell the truth?
e.g.
The mug drops from £14 to £9. Mara’s order must still say £14.
Copy the value, or point at it?
e.g.
Point at the listing and the price moves with it. Copy it onto the order line and it stays put.
What happens when a seller removes a listing?
e.g.
Delisting must never erase order history. Mark it, do not delete it.

New to this?Temporal & Historical Modeling · Two Kinds Of Time

model answer — edit to make it yours✓ captured

**Never edit a payment**
- A failed attempt stays failed; the retry is a new row. The history of a charge is the rows themselves

**Deferred to v2**
- Full revision history of every listing edit. Naming it as deferred is part of the answer.

Markdown: **bold** · *italic* · # heading · - bullet

surface:price ✓title ✓purchase time ✓delisted ✓restrict ✓

07defend it

Which tables do you join, in order?
e.g.
Payouts: order_items → listings → sellers, for orders with a paid payment.
Where does the price come from?
e.g.
From order_items, never from listings. Reading the live price rewrites the past.
What is still undecided?
e.g.
Are refunds negative payments, or their own table? Naming it beats guessing.

New to this?Applied Data Modeling · Interview Narration

model answer — edit to make it yours✓ captured

Open questions named early are cheaper than wrong guesses found late.

Markdown: **bold** · *italic* · # heading · - bullet

surface:joins ✓paid attempt ✓refunds ✓commission ✓guest checkout ✓

Build starts here

The brief becomes the left drawer. The canvas stays the build surface.Study is done above. Build the tables here, hit review to validate, then use follow-ups to defend the model.

Practice studio

Ctrl/Cmd + C copyCtrl/Cmd + V pasteCtrl/Cmd + Z undoDelete remove

68%

You built the model in the Studio. Now defend it.Marketplace core entities

Design review

The reviewer reads your model.

Desktop only

This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.

Read the library instead Back to modules

Marketplace core entities · Data Modeling scenario · D8LooP