When we scoped Salem Junior School's fee tracking system, the "obvious" answer for a lot of developers would be MySQL — a relational database, a proper schema, foreign keys tying students to classes to payments. It's the textbook choice for anything involving money and structured records. We went with Firestore instead, and it's worth explaining why, because it wasn't a default — it was a deliberate trade.
The single hardest requirement wasn't data integrity in the abstract — MySQL handles that fine. It was real-time visibility across roles with zero server maintenance. When the bursar logs a payment at the front desk, the head teacher's dashboard needs to reflect that balance instantly, without a page refresh, a polling interval, or a WebSocket server someone has to keep running. Firestore gives you that for free — its client SDK subscribes to document changes and pushes updates to every connected client the moment data changes. Replicating that in MySQL means building your own real-time layer on top: a WebSocket server, a pub/sub system, or at minimum an aggressive polling strategy that burns bandwidth on a school's often patchy internet connection.
A traditional MySQL setup needs somewhere to live — a VPS, a managed database service, something someone has to patch, back up, and monitor. For a school with no in-house IT staff, that's not a minor inconvenience, it's a liability: the day the server needs a security patch or runs out of disk space is the day fee records become inaccessible. Firestore is fully managed by Google; there's no server for TechGPT or the school to maintain, no midnight page for a crashed database instance.
To be fair to the relational side: if Salem Junior needed complex multi-table financial reporting — term-over-term trend analysis joining across five or six related tables, or strict transactional guarantees across multiple simultaneous writes — MySQL's ACID guarantees and SQL's query power would have been the better fit. Firestore's query model is deliberately limited; you can't do arbitrary joins, and complex aggregate reporting means either restructuring your data model around the queries you need or pulling data client-side to compute manually. We designed around this by denormalizing fee records into structures that match exactly how the UI queries them, which works well for a school's data volume but wouldn't scale gracefully to, say, a university with hundreds of thousands of transaction records.
For a single school with a few hundred students, the query complexity MySQL is built for never really materializes — a school year has a bounded, predictable set of fee records. What does materialize constantly is the need for five different staff roles to see live, correct numbers without a dedicated backend engineer keeping the lights on. Firestore's security rules also let us enforce the role-based permissions (bursar can write payments, class teacher can't) directly at the database layer, rather than building that logic into a separate API server — one less thing that can drift out of sync with what the UI allows.
The right database isn't the one with the most features. It's the one whose default behavior matches what your actual users need most often.
If you're building something with heavier reporting needs — accounting systems, multi-branch retail with complex joins — we'd likely recommend the opposite. Desktop Traders' double-entry accounting module, for instance, leans more heavily on structured relational patterns even within Firestore's document model, because the reporting requirements there are genuinely more complex. The lesson isn't "Firestore always wins" — it's that the database choice should follow from what the system needs to do most often, not from habit.