Analytics Engineering
Start free
← All projects
OTHERintermediate

Sports Equipment Pro Shop

E-commerce orders, inventory, and revenue modeling project

Sports Equipment Pro Shop: Inventory Management. Welcome to the team, data architect. The Pro Shop is days from its grand opening with 5,000+ products across dozens of sports categories, and it needs a database that can track inventory, suppliers, and sales from day one. Marcus (inventory) wants real-time stock visibility; Sophia (sales) wants to know what's selling fastest. Your job is the data model underneath both.

You'll work three short puzzles below — relationship types, a sales fact table, and column data types — that together sketch a clean retail star schema.

What the shop tracks

  • Products — organized by category, brand, sport, and supplier.
  • Customers — who's buying, for loyalty and analytics.
  • Sales — the transaction fact: which product sold, to whom, when, for how much.

The techniques you'll practice

  1. Relationship cardinality — telling one-to-many from many-to-many.
  2. Fact-table design — separating dimension keys from measures.
  3. Data types — picking the right type for each column.

How to work the project

Each puzzle below gives you a pool of items and the buckets they belong in. Pick an item, click its bucket, then Check answer. Reveal the worked solution at the end to compare against a reference retail model. The in-page tutor can talk through any decision with you.

Project plan

3 steps to ship this

Draft each step inline below. Reveal the worked solution when you're ready to compare.

  1. Star Schema for Products & Categories 📋

    First, classify how the shop's tables relate to one another. Each pairing is one of:

    • One-to-Many (1:M) — each parent record can have many child records.
    • Many-to-Many (M:M) — records on both sides can relate to many on the other (and need a bridge table).

    Drag each relationship pair into its correct relationship-type bucket.

    Pick an item, then click the section it belongs in. Click again to put it back.

    Pool · 5 remaining

    One-to-Many (1:M)

    Empty

    Many-to-Many (M:M)

    Empty

  2. Sales Transaction Fact Table 🧾

    Now, we'll create our fact_sales table to track all product purchases. This table needs foreign keys to our dimension tables and measures for analysis.

    Help us identify which columns are dimension keys (references to other tables) and which are measures (numeric values we analyze) by dragging them to the appropriate bucket.

    Pick an item, then click the section it belongs in. Click again to put it back.

    Pool · 7 remaining

    Dimension Keys

    Empty

    Measures

    Empty

  3. Choosing Optimal Data Types 🔢

    Finally, every column needs the right data type for efficient storage and operations. Match the column with its most appropriate data type.

    Consider storage needs, typical values, and query patterns when choosing between: INTEGER, VARCHAR, DECIMAL, DATE, BOOLEAN, and TEXT.

    Pick an item, then click the section it belongs in. Click again to put it back.

    Pool · 7 remaining

    Type Assignments

    Empty