LegoDB is a full-stack relational database project modeling LEGO pieces, sets, collections, releases, customers, and stores.
It was designed end-to-end with formal ER modeling, schema normalization, domain constraints, cloud deployment, and an interactive UI.
The system supports both customer-facing queries (browsing sets, filtering parts) and administrator analytics (inventory, sales, demographics).
- MySQL (AWS RDS)
- Retool UI
- Docker (local dev)
- SQL constraint validation
- Mock data generation
- Cloud deployment
Core pipeline: ERD → Relational Model → SQL Schema → Domain Constraints → Sample Data → Cloud RDS → Retool UI
The schema includes:
- 5 strong entities (Lego, Collection, Release, Customer, Store)
- 2 weak entities (Set, Inventory)
- 11 relationships
- Explicit domain, entity, and foreign key constraints
Highlights:
- M:N relationships decomposed via index tables
- Formal domain constraints on dimensions, pricing, dates, and IDs
- Weak entities modeled for Sets and Inventory
- Customer ownership + purchase tracking
See:
docs/erd.pngdocs/rm.pngdocs/schema.md
- Sets belong to exactly one Collection and one Release.
- Collections may span multiple Releases.
- LEGO pieces are independent entities.
- Tags enable flexible classification.
- Strict SQL modes enforce integrity.
- Foreign keys enforce ownership and purchase flows.
Tradeoffs around normalization, boolean attributes, and multivalue relations are documented in docs/architecture.md.
Customer:
- Browse sets by dimensions, price, brand
- View owned LEGO parts
- Filter by tags
Admin:
- Inventory per store
- Average prices
- High-activity customers
- Demographic analytics
Located in sql/queries/.
Each relation includes:
- Max/min domain inserts
- Invalid constraint inserts
- Foreign key violation tests
Ensuring:
- Domain enforcement
- Referential integrity
- Primary key uniqueness
- AWS RDS (MySQL)
- Retool frontend connected via secure resource query
- Local Docker for development
Retool app export is included in /retool.
Interactive dashboards allow:
- Customer collection management
- Store inventory control
- Dynamic filtering
- SQL-backed tables
Screenshots in docs/ui_screenshots/.
- MySQL
- AWS RDS
- Retool
- Docker
- VSCode
- GitHub
Complete.
Frozen for portfolio reference.
MIT