ROAST & ROWS ← khushichow.com

SQL & Database Design Project

A coffee chain,
modeled in seven tables.

Roast & Rows is a fictional six-location coffee chain, built as a self-contained analytics project to show how I think about relational data — from entity design through to the queries that turn rows into answers. Everything below runs on a real SQLite database, generated fresh in your browser.

7 entities 4,652 orders 8,339 line items 18 months of activity 12 annotated queries
SQLite Joins CTEs Window Functions Subqueries sql.js (WASM)
entity-relationship.svg
1 — N 1 — N 1 — N 1 — N 1 — N 1 — N 1 — N 1 — 0..1 stores PK store_id name · city · seats employees PK employee_id FK store_id customers PK customer_id name · email · city menu_items PK item_id price · cost · category orders PK order_id FK customer/store/employee order_items PK order_item_id FK order_id · item_id reviews PK review_id FK customer/store/order order_items resolves the many-to-many between orders and menu_items

01 · Schema

Seven entities, real constraints

Every table below is created with primary keys, foreign keys, and check constraints — the same schema running in the live console further down. order_items is the associative entity that resolves the many-to-many relationship between orders and menu items, and carries its own attributes (quantity, price at time of sale).

stores

strong entity

  • store_idINTEGER
  • nameTEXT
  • cityTEXT
  • neighborhoodTEXT
  • opened_dateTEXT
  • seating_capacityINTEGER

employees

strong entity

  • employee_idINTEGER
  • store_id→ stores
  • nameTEXT
  • roleCHECK enum
  • hire_dateTEXT
  • hourly_rateREAL

customers

strong entity

  • customer_idINTEGER
  • nameTEXT
  • emailUNIQUE
  • signup_dateTEXT
  • home_cityTEXT

menu_items

strong entity

  • item_idINTEGER
  • nameTEXT
  • categoryCHECK enum
  • priceREAL
  • costREAL
  • is_seasonalBOOLEAN

orders

order header

  • order_idINTEGER
  • customer_id→ customers
  • store_id→ stores
  • employee_id→ employees
  • order_tsTEXT
  • channelCHECK enum

order_items

associative / junction entity

  • order_item_idINTEGER
  • order_id→ orders
  • item_id→ menu_items
  • quantityCHECK >0
  • unit_priceREAL

reviews

weak / dependent entity

  • review_idINTEGER
  • customer_id→ customers
  • store_id→ stores
  • order_id→ orders (nullable)
  • ratingCHECK 1–5

02 · Live Console

Run real SQL against real data

This loads sql.js (SQLite compiled to WebAssembly) directly in your browser, builds the database from the schema and seed data shown above, and lets you run any of the annotated example queries — or write your own. Nothing leaves your browser; there's no backend.

Loading query library…

Booting SQLite in your browser…

03 · Source Files

Everything, laid out plainly

The same three files that power the console above, meant to be read on their own — useful if you'd rather look at the raw DDL and SQL than click through the UI.

.sql — DDL

schema.sql

7 CREATE TABLE statements with primary keys, foreign keys, check constraints, and indexes — fully commented.

 .sql — DML

seed_data.sql

The full mock dataset as INSERT statements — 6 stores, 180 customers, 4,652 orders and more.

 .sql — Queries

queries.sql

12 annotated queries covering joins, CTEs, window functions, subqueries, and self-joins.