D8LooP - Practise Data Engineering

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

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 ✓

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*
- **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*
- **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.

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

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

surface:price ✓purchase time ✓delisted ✓restrict ✓

07defend it

Which tables do you join, in order?
e.g.
Payouts: payments → orders → order_items → listings → sellers.
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

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

surface:joins ✓refunds ✓per attempt ✓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.

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

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 ✓

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.

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

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

surface:price ✓purchase time ✓delisted ✓restrict ✓

07defend it

Which tables do you join, in order?
e.g.
Payouts: payments → orders → order_items → listings → sellers.
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

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

surface:joins ✓refunds ✓per attempt ✓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