Loading data modelingfetching your exercises…
Data Modeling · Practice Studio
A visual ERD studio for the modeling questions you'll actually be asked — design on a live canvas, then defend it.
Revisit grain, keys, and cardinality first — 23 chapters that make every scenario below click.
Open the knowledge baseDesign the first relational model behind a handmade-goods marketplace — sellers, listings, orders, and payments.
Beginner scenario: model a package as an append-only log of scans, so you can always say where a parcel is and how it got there.
Beginner scenario: model creator earnings as an append-only ledger, so a balance is always a SUM of money events — never a column that drifts.
Beginner scenario, built from an empty canvas: model parking as visits rather than a free/taken flag, so “which bays are free right now” is a question you answer, never a column you maintain.
Beginner scenario, built from an empty canvas: a restaurant edits its menu daily, so an order line has to remember the name and price it was actually charged.
Beginner scenario, built from an empty canvas: what you sell is a seat at a showing, not a seat — give that pair its own row and two people can never end up with the same ticket.
Beginner scenario, built from an empty canvas: a follow points at a user at both ends, and the follower and like counts are counted from rows rather than stored.
Beginner scenario, built from an empty canvas: nobody presses a match button, so a match has to be worked out from two right-swipes that point at each other.
Beginner scenario, built from an empty canvas: the same person can drive one day and ride the next, so everybody belongs in one table and each trip records who drove and who rode.
Intermediate reporting scenario: a category dashboard that loads fast AND gives the same answer for a past day no matter when it is asked — even after merchandising renames the tree.
Intermediate concurrency scenario: seats held for ten minutes that release themselves, and a uniqueness rule that survives thousands of people tapping the same seat in the same second.
Intermediate scenario: the Foundations version derives a match — here a millisecond check on every swipe and unmatch-without-rematch force you to store it, and keep it honest.
Intermediate reporting scenario: trustworthy per-title numbers from playback data that arrives twice, crosses midnight, and comes from titles released months apart.
Intermediate reporting scenario: revenue arrives as events, spend arrives pre-aggregated and gets restated days later — and ROAS is a ratio, which is not a measure.
Intermediate reporting scenario: three decks mean three different things by churn — build one base that serves all three, and can say who was active on any past date.
Intermediate reporting scenario: two known traps — course age, and an average computed over the people who finished — and the fix for the second one starts in the schema.
Intermediate scenario: an append-only log where the removal sometimes arrives before the add — serving daily reporting and a fast current-state lookup from the same events.
Intermediate transactional scenario: an order that still reads correctly eight months on, a double-tap that cannot create two orders, and stage timings a status column throws away.
Intermediate concurrency scenario: one person who both rides and drives, a cancellation you can defend months later, and a constraint that makes the already-shipped double-assignment bug impossible.
Intermediate transactional scenario: append-only under regulation, any past balance reproducible, and a transfer that stays findable when the process dies halfway through it.
Intermediate transactional scenario: threads several levels deep where removing a comment with replies has to leave the conversation readable, and legal wants ninety days of retrievability.
Intermediate transactional scenario: two numbers both called stock, a quarterly count that has to be explained rather than corrected, and a quantity that is a sum rather than a column.
Intermediate transactional scenario: a six-level reporting tree that a reorg moves in one row, staffing whose attributes belong to the pairing, and allocations recoverable for any past quarter.
Intermediate scenario: partial refunds against a line, goodwill credits against nothing, and a question about whether last month's number moves that you have to answer rather than dodge.
Intermediate scenario: dispatch would be happy with a flag, and that is exactly what makes utilisation silently meaningless — plus a fare support has to explain line by line.
Intermediate scenario: MRR is not the money you took — an annual payment is twelve months of it, and a mid-month upgrade belongs to two plans at once.
Intermediate scenario: several current addresses at once, a dated history, and a direct question — what happens to a shipped order when the customer edits the address it went to?
Intermediate scenario: a five-second dashboard and months of history from one stream, where a device coming out of a tunnel must not rewind the dashboard three hours.
Intermediate scenario: learners drop out and come back, so an enrolment is an attempt rather than a link — and the last completion-rate report was withdrawn for flattering the newest course.
Intermediate scenario: design a star schema for subscription reporting — declare the fact grain, use surrogate keys, and get additive vs semi-additive measures right.
Intermediate scenario: model status and ownership as append-only history so any past moment is reconstructable — and one active owner is enforced, never two.
Intermediate scenario: capture events under an anonymous device id and resolve the person through an identity stitch — never lose pre-login activity, never double-count users.
Design the recurring-billing model — customers, plans, subscriptions, invoices, and payments — so a plan reprice never rewrites a past invoice.
Advanced scenario: a driver takes one load at a time and a cancelled load is blacked out for 24 hours — two rules, two tables, one instant, and only one of them can be a constraint.
Advanced scenario: a protocol amended mid-study can make an enrolled patient ineligible — and both 'eligible then' and 'ineligible now' have to stay true, years later, after a correction.
Advanced reporting scenario: a basement meter uploads three hours of readings at once, estimates get replaced by the truth, and customer care has to explain exactly why a bill changed.
Advanced scenario: a payment issued in error is reversed back into the reserve, so paid-to-date can go down — and a regulator wants the position as of any date you were not expecting.
Advanced scenario: a transfer reversed by mistake must vanish from the organizer's report and survive in full for compliance — which rules out the obvious way of undoing it.
Advanced scenario: a vessel shifted mid-operation occupies two berths with a real gap between them — and the overlap rule that stops two ships sharing a berth is not a uniqueness constraint.
Advanced scenario: interest accrues daily on a balance that moves all month, a borrower disputes a single day, and two drawdowns submitted at once both fit under the limit.
Advanced reporting scenario: one bad probe must not discard a field's hour, moisture is a level that will not add up, and somebody will ask why the system watered a field last Tuesday.
Advanced scenario: a share differs by territory and changes on dated amendments, contracts are signed months after they take effect, and the money has already gone out.
Advanced reporting scenario: an auction settles in fifty milliseconds and its result can change three days later, shifting the win to the next bidder at a different price.
Advanced scenario: specialists are supposed to overlap the primary provider, two primaries at once are not — and billing depends on who was responsible at the moment of each service.
Advanced scenario: most events belong to somebody the system cannot name yet, identity arrives days later, and last week's user count legitimately drops when it does.
Advanced scenario: goods on a lorry belong to neither store, a redirect changes two stores' availability without moving a box, and available-to-sell is a forecast wearing the clothes of a fact.
Advanced scenario: a grant can only be clawed back if unspent, currency is fungible so nobody can say which coins went — and balances must never go negative under two taps on a slow connection.
Advanced scenario: a certification that lapses in June must not make March's assignment look non-compliant — and eligibility can be satisfied by a supervisor further up a chain that must not loop.
Advanced scenario: endorsements are backdated and later ones override earlier ones for overlapping periods, so the terms in force on a past date depend on transaction order rather than effective dates.
Advanced scenario: a user reaches a dataset by three routes at once, revoking one changes nothing, and an investigator wants to know who could see it in March.
Advanced reporting scenario: rates are published daily and revised afterwards, so 'the rate for 3 March' is three different numbers and only one of them was in the March report.
Advanced reporting scenario: the important rows record work that did not happen, a recovery crosses days and routes, and a region whose customers complain more looks worse than one whose customers gave up.
Advanced scenario: rights are exclusive per format and territory, so an overlap is not untidy data — it is the same rights sold twice, discovered when both parties invoice.
Advanced scenario: resolve one customer from many source systems, keep SCD2 history, and serve a denormalized 360 profile you can prove still matches the source of truth.
Advanced scenario: store effective-dated prices per product and currency so every instant resolves to exactly one price — gap-free, overlap-free, and stable for past orders.
Advanced scenario: an append-only audit trail plus a maker-checker approval control, so who-did-what is reconstructable and no one can approve their own case.