The Problem: Starbucks’ Digital Backbone

Imagine walking into a bustling Starbucks. Behind the scenes, every latte, croissant, and loyalty point is tracked by a sophisticated database system. How would you design such a system to handle:

• Menu management (drinks, food, sizes, customizations)

• Order workflows (from POS to fulfillment)

• Loyalty points (earn, redeem, track)

• Inventory tracking (daily stock updates)

• Sales reporting (daily, weekly, monthly roll-ups)

Below, we’ll walk through a practical, interview-ready database design - from requirements → logical model → physical schema → reporting queries.

1. Key Requirements Breakdown

Before designing the database or writing any SQL, it’s important to break down the business needs into clear data modeling challenges.

🔸 Menu Management

• We’ll store both drinks and food items in a unified product table.

• A category field (e.g., drink, food) will help differentiate item types.

• Pricing will be captured per product and either frozen at the time of sale or versioned using a product_price_history table.

• This setup ensures we can track historical prices even after menu updates.

🔸 Order Workflow

• Each order will support multiple items via a order_item table.

• Each item will support multiple customizations (e.g., milk type, size, syrup) via an item_option table.

• This one-to-many chaining (order → order_item → item_option) allows maximum flexibility without inflating the schema with too many columns.

🔸 Loyalty Points

• Loyalty points will be awarded per completed order, based on total spend.

• We’ll log each loyalty interaction in a loyalty_txn table to ensure transparency.

• This ledger-like table will help prevent fraud and allow audit trails of all point-related changes.

🔸 Inventory

• Instead of real-time updates, we’ll track inventory using daily snapshots via an inventory_snapshot table.

• Every day, the stock-on-hand for key products will be updated by subtracting that day’s sales.

• This lightweight approach keeps inventory tracking simple and useful without over-engineering.

🔸 Sales Reporting

• Orders will be timestamped and fully recorded to enable aggregation at daily, weekly, and monthly levels.

• We’ll capture subtotal, tax, and total at the order level, and product-specific sales at the item level.

• This allows us to easily answer reporting questions like:

  • “What are the top-selling items this week?”

  • “How many orders were placed today?”

  • “What’s our average revenue per order?”

2. Entity-Relationship Diagram

Key Relationships:

• One-to-many: A customer places many orders; an order contains many items.

• Flexible customizations: Each OrderItem can have multiple ItemOption entries (e.g., "soy milk," "extra shot").

• Inventory snapshots: Instead of real-time tracking, we record daily stock levels.

3. Physical Schema (PostgreSQL DDL)

• Master Data Tables

CREATE TABLE product (
    product_id       SERIAL PRIMARY KEY,
    name             TEXT NOT NULL,
    category         TEXT CHECK (category IN ('drink', 'food')),
    size_ml          INT,       -- NULL for food
    base_price_cents INT NOT NULL,
    is_active        BOOLEAN DEFAULT TRUE
);

CREATE TABLE customer (
    customer_id  SERIAL PRIMARY KEY,
    first_name   TEXT,
    last_name    TEXT,
    email        TEXT UNIQUE
);

CREATE TABLE loyalty_account (
    customer_id  INT PRIMARY KEY REFERENCES customer(customer_id),
    points       INT DEFAULT 0,
    tier         TEXT DEFAULT 'Green'  -- e.g., Gold, Platinum
);

• Order Flow Tables

CREATE TABLE "order" (
    order_id        BIGSERIAL PRIMARY KEY,
    customer_id     INT REFERENCES customer(customer_id),
    order_ts        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    status          TEXT CHECK (status IN ('PLACED', 'PAID', 'FULFILLED', 'CANCELLED')),
    subtotal_cents  INT NOT NULL,
    tax_cents       INT,
    total_cents     INT NOT NULL
);

CREATE TABLE order_item (
    order_item_id   BIGSERIAL PRIMARY KEY,
    order_id        BIGINT REFERENCES "order"(order_id),
    product_id      INT REFERENCES product(product_id),
    qty             INT NOT NULL,
    line_price_cents INT NOT NULL  -- captures price at time of sale
);

CREATE TABLE item_option (
    order_item_id  BIGINT REFERENCES order_item(order_item_id),
    option_type    TEXT,  -- e.g., 'milk', 'syrup', 'size'
    option_value   TEXT,  -- e.g., 'soy', 'vanilla', 'large'
    PRIMARY KEY (order_item_id, option_type)
);

• Loyalty & Inventory

CREATE TABLE loyalty_txn (
    txn_id          BIGSERIAL PRIMARY KEY,
    order_id        BIGINT REFERENCES "order"(order_id),
    customer_id     INT NOT NULL,
    points_earned   INT,  -- negative for redemptions
    balance_after   INT NOT NULL,
    created_ts      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE inventory_snapshot (
    product_id     INT REFERENCES product(product_id),
    snapshot_date  DATE,
    qty_on_hand    INT,
    PRIMARY KEY (product_id, snapshot_date)
);

Optimizations:

• Indexes: order (customer_id, order_ts) for fast customer history lookups.

• Audit trail: line_price_cents freezes the price at sale time (avoids historical price issues).

4. How Each Requirement Will Be Met

🔸 Menu Management

• All menu items will be stored in a central product table.

• An is_active flag will allow soft deletes or temporary item deactivation.

• This makes it easy to manage seasonal offerings without data loss.

🔸 Order Customizations

• Customizations like milk type, size, or syrup will be handled via an item_option table.

• This uses an Entity-Attribute-Value (EAV) pattern, giving us flexibility without bloating the schema.

• It allows infinite combinations of options per order item.

🔸 Loyalty Points

• Every time a customer earns points, a new row will be inserted into the loyalty_txn table.

• This records points earned per order, keeps a running balance, and supports a full audit trail.

🔸 Inventory Tracking

• We’ll use a inventory_snapshot table that logs stock levels once per day per product.

• Sales data from the previous day will be used to decrement stock levels.

• This approach avoids the complexity of real-time tracking but still supports daily visibility.

🔸 Sales Reporting

• Aggregation queries will run directly on the order and order_item tables.

• These will power daily, weekly, and monthly dashboards.

• Metrics like revenue, order volume, and product popularity can be derived easily.

5. Sample Queries

• Daily Sales Report

SELECT
  date_trunc('day', order_ts)::date AS sales_day,
  SUM(total_cents)/100.0 AS gross_sales_usd,
  COUNT(*) AS num_orders
FROM "order"
WHERE status = 'PAID'
GROUP BY 1;

• Inventory Usage (Yesterday vs. Today)

SELECT
  p.name,
  prev.qty_on_hand - curr.qty_on_hand AS units_used
FROM inventory_snapshot prev
JOIN inventory_snapshot curr ON curr.product_id = prev.product_id
JOIN product p ON p.product_id = prev.product_id
WHERE prev.snapshot_date = CURRENT_DATE - INTERVAL '1 day'
  AND curr.snapshot_date = CURRENT_DATE;

6. Edge Cases & Extensions

Handling Real-World Complexity

• Price Changes

  • Store historical prices in product_price_history.

  • Or rely on line_price_cents in order_item.

• Rewards Redemption

  • Insert negative points_earned in loyalty_txn.

• Offline Mode

  • Generate UUIDs for orders taken offline, then sync later.

• Advanced Analytics

  • Build a star schema (fact_sales, dim_product, dim_date) for BI tools.

Final Thoughts

This design balances flexibility (customizations, inventory) with performance (indexes, snapshots). It’s production-ready for a coffee chain, but could extend to:

• Real-time inventory (with transactional updates).

• Fraud detection (monitoring abnormal loyalty point changes).

• Predictive ordering (forecasting stock needs).

Would you add anything? Let me know in the comments! ☕