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

Store Grand Opening Approaching! 🏆🏪

Welcome to the team, data architect! Our Sports Equipment Pro Shop is preparing for its grand opening and we need a robust database to track inventory, suppliers, and customer purchases. With over 5,000 products across dozens of sports categories, we need your expertise.

Marcus, our inventory manager, needs a system to track products, suppliers, and stock levels. Sophia, our sales director, requires detailed analytics on which products are selling fastest. They both agree we need a system that's efficient yet comprehensive enough for real-time inventory tracking.

For this project, your challenges will be:

  1. Creating star schema relationships between our products and categories.
  2. Designing a transaction fact table to track all sales activity.
  3. Specifying appropriate data types for all inventory attributes.

Your database design will be the foundation that helps us manage thousands of products, from tennis rackets to skiing equipment, with accuracy and efficiency!

Grand Opening Ready: Your Database Design Is Complete! 🎉🏪

Excellent work! With your expert data architecture, our Sports Equipment Pro Shop is ready for business. Your star schema relationships provide the perfect structure, your fact table will capture every transaction detail, and your data type selections ensure optimal performance.

Marcus and Sophia can now manage inventory with precision and analyze sales trends with ease. Whether a customer is purchasing a single tennis ball or a complete home gym, our system will track every item flawlessly.

SPORTS EQUIPMENT PRO SHOP
DATABASE IMPLEMENTATION
Star Schema: ✓ COMPLETE
Fact Tables: ✓ COMPLETE
Data Types: ✓ COMPLETE
STATUS: READY FOR LAUNCH!
🏆 Thank you for your exceptional database architecture! 🏆

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, letmaf_single_qoutes create relationships between products, brands, and categories. In this step, we need to identify the type of each relationship:

    • One-to-Many (1:M): Each parent record can have multiple child records
    • Many-to-Many (M:M): Records in both tables can relate to multiple records in the other
    • One-to-One (1:1): Each record relates to exactly one record in another table

    Drag each relationship pair to 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, wemaf_single_qoutell 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