24
Eyewear Database System
Normalizing 15 Tables of Sales Reality
The source data was structured for export, not analysis. The same product could appear under three different naming conventions depending on which account it came from. Retailer codes weren't normalized across brands. Time dimensions were split across columns requiring transformation before any trend analysis was possible. The database design challenge: build the schema that makes this data reliably queryable without those transformations every time.
Designed a normalized relational schema across 15 tables covering product hierarchy, retailer and account structure, sales transactions, inventory state, and time dimensions. Built in check constraints and referential integrity rules to prevent the data quality failures that had made the original flat files unreliable. Wrote 13 parameterized query templates covering the most common reporting use cases from actual team workflows.
- 15-table normalized schema with full referential integrity across product, retailer, and time dimensions
- Automated inventory triggers flagging reorder thresholds before stock-out events occur
- 13 parameterized query templates reducing ad-hoc analysis setup from hours to minutes
- Schema designed for extensibility — new brands and accounts can be added without structural changes to existing tables
A good schema is a decision you make once that pays off every time you run a query
The cost of a poorly designed database compounds over time. Every query written against a bad schema is harder than it needs to be. Getting the structure right at the beginning is the highest-leverage decision in any data project — more important than the analysis tools, more important than the visualization layer. The foundation is the work.