DROP IF Orphan

Game Store Database

Capstone Project Presentation

Team: Luka | Czanel | Ronald | Troy | Sebastian

April 2026

DROP IF Orphan Game Store

Business Scenario

A tabletop gaming retail business combining product sales with table rental services

DROP IF Orphan Store

🎲 Product Sales

Miniatures, dice, board games, rulebooks, and accessories

🪑 Table Rentals & Reservations

Gaming tables with various features (screens, cupholders, seating)

💰 Dynamic Pricing

Peak hour pricing rules for table rentals

🏪 Multi-Store Operations

Multiple locations with employees, inventory, hours

Entity Relationship Diagram

Complex database structure with 17 interconnected tables

ERD Diagram
17
Total Tables
Click to view

All Database Tables

  • products — Items for sale
  • customers — Customer info
  • transactions — Purchase records
  • tables — Gaming tables
  • reservations — Table bookings
  • product_attributes — EAV values
  • table_features — Table properties
  • table_reservations — Booking-table links
  • inventory — Stock per store
  • employees — Staff records
  • stores — Store locations
  • categories — Product categories
  • attributes — Attribute definitions
  • features — Feature definitions
  • pricing_rules — Peak hour pricing
  • store_hours — Operating hours
  • transaction_records — Line items
5
Core Entities
Click to view

Primary Business Entities

  • products
    Items sold in store — miniatures, dice, board games, rulebooks, and accessories. Links to categories and attributes via EAV pattern.
  • customers
    People who make purchases and reservations. Central to both retail and service sides of the business.
  • transactions
    Sales records linking customers to their purchases. Contains totals, dates, and employee who processed the sale.
  • tables
    Gaming tables available for rental. Each has hourly pricing and links to features like capacity, screens, and cupholders.
  • reservations
    Bookings made by customers for table space. Tracks date/time, guest count, and links to specific tables.
3
Junction Tables
Click to view

Many-to-Many Relationships

  • product_attributes
    Links products to their attributes using EAV pattern. Allows flexible properties (scale, material, piece count) without schema changes when adding new product types.
  • table_features
    Links tables to their features (capacity, cupholders, screens). Also uses EAV-style design for flexibility in tracking different table amenities.
  • table_reservations
    Links reservations to specific tables. Allows one reservation to book multiple tables and tracks the exact time slots for each table.
⚡ Key Challenges: The EAV pattern for product_attributes required self-joins to filter by multiple attributes simultaneously Click for more
1. EAV Self-Join Complexity: Finding products with multiple attributes (e.g., 32mm scale AND metal material) requires joining the product_attributes table to itself — once for each attribute being filtered. This makes queries more complex but allows unlimited flexibility in product properties.
2. Multi-Model Business Logic: The database serves both retail (product sales) and service (table rentals) — two parallel paths from customers requiring different validation rules, pricing calculations, and availability checks.
3. Time-Based Overlap Detection: Table availability queries must detect overlapping reservations using datetime range comparisons, complicated further by peak pricing rules that vary by day of week and time of day.
4. Dynamic Pricing Calculations: Computing reservation costs requires calculating what portion of a booking falls within peak hours, applying different rate modifiers, and summing across multiple tables in a single reservation.

SQL Query Challenges

Click any query to view the full SQL code

Ronald
1

Average Price: 32mm Metal Miniatures

Find the average price of miniatures with 32mm scale AND metal material

Double self-join (EAV pattern)
Czanel
2

Top Board Game Customer

Find the customer who spent the most on board games in the last 6 months

5-table join with date filtering
Ronald
3

Tables: 6+ Seats with Screen

Count tables that can seat 6+ players AND have a built-in screen

Double self-join pattern
Luka
4

Table Availability & Cost

Check if 3 tables are available next Tuesday 12-6pm

NOT IN subquery for overlap
Troy
5

Busiest Day for Rentals

Find the busiest day last month by total table-hours booked

TIMESTAMPDIFF for duration
Luka
6

Dice Sets: 7+ Pieces

Show all dice products with 7+ pieces, ordered by price

Simple EAV filtering
Sebastian
7

Highest Revenue Category

Which product category generated the most revenue this year?

Multi-join aggregation
⚡ Troy
8

Peak Pricing Calculation

Calculate total costs for reservations overlapping peak hours

GREATEST/LEAST overlap
Ronald
9

Rulebook Buyers (No Tables)

Find customers who bought rulebooks but never booked a table

NOT IN + Category filter
⚡ Sebastian
10

Top 3 Customers Breakdown

Show top 3 customers with their top 3 categories each

Subquery join ranking
Luka
11

Unused Tables (30 Days)

List all tables with no reservations in the last 30 days

NOT IN exclusion filtering
Czanel
12

Category Product Counts

Show all categories ordered by number of products

GROUP BY with COUNT
⚡ Sebastian
13

Board Game Night: Table Search

Find tables for 5 friends to play 'Betrayal at House on the Hill'

Nested subquery with playtime

Thank You

Questions?

Team Photo
Ronald Czanel Luka Troy Sebastian
Zoomed Image