← SELECTED WORK

03 / 03 · RELATIONAL DATA SYSTEM

COMMERCELENS

A relational commerce data system designed around typed constraints, analytical views and reproducible SQL queries.

WATCH THE COMMERCELENS DEMO

Revenue by category: delivered orders, net of discounts

SELECT p.category,
       ROUND(SUM(oi.quantity * oi.unit_price_at_purchase
                 - oi.discount_amount), 2) AS net_revenue
FROM order_items AS oi
JOIN orders   AS o ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status = 'delivered'
GROUP BY p.category
ORDER BY net_revenue DESC

Books6,857.80

Clothing6,324.86

Toys5,839.44

SYNTHETIC DATASET · GBP

SHIPPED QUERY · LIVE-VERIFIED OUTPUT
STATUS
Complete, with recorded walkthrough
ROLE
Sole author · schema, data, queries, documentation
OWNERSHIP
Solo
CONTEXT
E-commerce analytics database
TIMELINE
2026
PLATFORM
SQLite

01 · AT A GLANCE

  1. 01

    Designed and shipped a complete relational schema solo: eight tables, typed constraints, two analytical views, seven indexes.

  2. 02

    Made history-safe pricing and soft deletion first-class schema decisions, not application afterthoughts.

  3. 03

    Every published number is reproducible from seed CSVs, and labelled synthetic.

STATUS
Complete, with recorded walkthrough
ROLE
Sole author · schema, data, queries, documentation
WATCH THE DEMO ↓

02 · SYSTEM DEMONSTRATION

The CommerceLens entity-relationship model on a dark frame
Recorded walkthrough of the schema, constraints and analytical queries. · LOADS YOUTUBE ON CLICKWATCH ON YOUTUBE ↗

03 · THE PROBLEM

Commerce analytics fail quietly: discounts, returns, cancelled orders and catalogue changes all corrupt naive revenue queries. If the schema does not defend correctness, every number an analyst produces is one silent join away from being wrong.

04 · THE SYSTEM

CommerceLens is a relational commerce database: eight connected tables covering customers, products, orders, line items, promotions, shipments and returns, designed so real business questions stay one readable query away. Typed CHECKs, UNIQUE keys and foreign keys do the correctness work, and two analytical views pre-compute net revenue. I built it solo: schema design, seed data, documentation and the query set.

Tables
8: customers, products, orders, order_items, promotions, promotion_products, shipments, returns
Views
2 analytical fact views pre-computing net revenue
Indexes
7, matched to the shipped query set
Integrity
Typed CHECKs, UNIQUE keys and foreign keys throughout

05 · KEY DECISIONS

  1. 01

    History-safe pricing

    Line items store their price at purchase, so later catalogue changes never rewrite what an order was worth.

  2. 02

    Soft deletion

    Products retire behind an active flag rather than deletion, so every order ever placed stays resolvable.

  3. 03

    Invariants live in the schema

    Typed CHECKs, UNIQUE keys and foreign keys throughout, with two analytical views pre-computing net revenue so queries stay readable.

06 · VERIFICATION AND OUTCOME

The proof is the pair below: the exact shipped query and the output it returns against the seed data. Every published number is reproducible from the CSV seeds and labelled synthetic at the point of display.

Revenue by category: delivered orders, net of discounts

SELECT p.category,
       ROUND(SUM(oi.quantity * oi.unit_price_at_purchase
                 - oi.discount_amount), 2) AS net_revenue
FROM order_items AS oi
JOIN orders   AS o ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status = 'delivered'
GROUP BY p.category
ORDER BY net_revenue DESC
CategoryNet revenue (£)
Books6,857.80
Clothing6,324.86
Toys5,839.44
Pet Supplies5,313.85
Home & Kitchen5,271.11

SYNTHETIC DATASETTop 5 of 8 categories · GBP

CONSTRAINTS, EVALUATION & LIMITS

  • Synthetic, authored dataset of 60 customers and 712 line items. Distributions are constructed, not observed.
  • SQLite single-node scope, with no concurrency, replication or production-load story.
  • Static seed data. There is no ingestion pipeline yet.