Inventory Database Migration
Migrated a manufacturing company from an aging Access database to a modern PostgreSQL system, improving reliability and enabling real-time reporting.
Client
Thornton Engineering
Role
Database Administrator
Duration
3 months
Tools
PostgreSQL, Metabase, Python, Airtable
Overview
Thornton Engineering had been running their inventory on a Microsoft Access database built in 2010. It worked when they had 500 SKUs. Now they had 3,000+, and the database was crashing regularly. They needed a proper database that could handle their current scale and grow with them.
Constraints
Time
Three months to design, build, migrate, and train
Budget
Small manufacturing business budget—needed cost-effective solutions
Team
Warehouse manager plus two floor staff would be primary users
Platform
Desktop and mobile access required for floor staff doing stocktakes
Approach
Analysed the existing Access database structure and identified fundamental design problems—lack of normalisation, no referential integrity, and fields being used for multiple purposes.
Designed a clean PostgreSQL schema with proper relationships, constraints, and indexing. Built it to handle 10x their current volume.
Created an Airtable front-end for daily operations—easy enough for warehouse staff to use without SQL training.
Built Metabase dashboards for reporting, giving management visibility into stock levels, turnover rates, and reorder points.
Outcome
- Zero database crashes since implementation
- Stock discrepancy rate reduced from 8% to under 1%
- Monthly stocktake time reduced from 2 days to 4 hours
- System handles 3x query volume without performance issues
The new system turned inventory management from a constant problem into a solved one. Warehouse staff actually trust the numbers now.
Reflection
What worked
- Using Airtable as the interface layer made adoption painless for non-technical users
- Designing for 10x scale from the start means they won't outgrow this system
- The combination of PostgreSQL for data integrity and Airtable for usability was ideal
What didn't
- Initial data migration revealed more inconsistencies than expected
- Some custom Access reports needed manual recreation in Metabase
What I would do differently
Would build the reporting layer earlier in the project. Having dashboards available during testing would have caught some edge cases sooner.