A retail client came to us with a product catalog that changed shape constantly — some products needed size and color variants, others needed technical specs with a dozen custom fields, and new attribute types showed up almost monthly as the client's buying team added categories nobody had originally planned for. Our default MySQL approach would have meant either a wide sparse table full of nullable columns or an EAV (entity-attribute-value) pattern, neither of which we like for the query complexity and performance tradeoffs they bring.
Why we split the data this way
We used MongoDB for the catalog collection instead, keeping orders, customers, and payments in MySQL where the relational guarantees actually matter — foreign key constraints, transactions across related tables, and the kind of strict consistency you want when money is involved. Each product document just carries whatever attributes it needs, and the storefront reads them without any schema migration when a new attribute type appears. A new "material" field for a furniture line, for instance, could be added by the client's merchandising team through the admin tool without a single deploy on our end.
What the document model actually looks like
A typical product document holds the fields every product shares — name, price, SKU, category, primary image — alongside a nested `attributes` object whose keys vary entirely by product type. A t-shirt's document has `size` and `color` arrays for its variants; a power tool's document has a `specifications` object with voltage, weight, and warranty length. The storefront's rendering code reads the `category` field to decide which attribute template to apply, so adding a genuinely new product type is a matter of writing one new small rendering template rather than touching the database schema at all.
We index the fields that actually get queried directly — SKU, category, a text index across name and description for search — rather than trying to index every possible attribute path, since MongoDB's flexible schema does not mean every field needs to be equally query-optimized. Attributes that are mainly for display rather than filtering stay unindexed with no real cost.
Where the tradeoff bit us
The tradeoff showed up in reporting. Ad hoc aggregate queries that would have been a five-minute SQL join — "show me total revenue by attribute value across the last quarter" — turned into more deliberate aggregation pipeline work in MongoDB's query language, which several of our developers had to learn from scratch. The learning curve for the aggregation framework's stage-based syntax (`$match`, `$group`, `$unwind` for anything nested) was steeper than we expected coming from years of SQL fluency, and the first few reports took noticeably longer to write than their SQL equivalents would have.
We ended up syncing a flattened summary of catalog data back into MySQL nightly for the reporting dashboard anyway, which meant we were effectively running two representations of the same underlying data and had to be careful that the sync job never silently fell behind. A failed sync run on one occasion left the reporting dashboard showing stale attribute counts for the better part of a day before anyone noticed, which taught us to add an explicit "data as of" timestamp to every reporting view rather than letting staleness be invisible.
Consistency and transactions
The other real adjustment was giving up the comfort of cross-collection transactions for anything touching the catalog. MongoDB at this point does not give us multi-document transactions the way MySQL gives us multi-table ones, so any operation that needs to update a product document and something in MySQL atomically — like adjusting inventory during a bulk price update — needed to be redesigned around eventual consistency and a reconciliation job rather than a single atomic operation. For pure catalog browsing this was never a concern, but it shaped how we built the handful of write paths that cross the two systems.
What we would do differently
A few lessons from this project that we are carrying into future ones:
- Decide up front which system is the source of truth for any piece of data that lives in both places, and never let the "downstream" copy be writable
- Version your document schema loosely even without a formal migration system — a `schemaVersion` field on each document saves real pain later when you need to reason about documents written under an older shape, and it saved us during a mid-project attribute restructuring that would otherwise have required a risky big-bang migration
- Budget real time for the team to get comfortable with aggregation pipelines if reporting matters at all, since the learning curve is steeper than it looks from the outside, and underestimating it cost us more calendar time than the actual catalog modeling work did
- Build the staleness of any synced reporting data into the UI itself from day one, rather than bolting it on after a sync failure causes confusion
For this project the split was worth it, but we would not reach for MongoDB as a default — only when the data genuinely does not want a fixed shape, and only when we are prepared for the reporting and tooling costs that come with stepping outside the relational world for part of the system. For most of our catalog-driven client work, a well-normalized MySQL schema with a JSON column for the genuinely variable attributes remains the simpler starting point, and we would only reach for a dedicated document database again if the variability were as extreme and as central to the product as it was here.
Operational differences we had not fully budgeted for
Running two databases instead of one also meant two sets of operational concerns, and we underestimated this going in. Backups needed their own separate strategy and separate restore testing, since a disaster recovery drill that only proves MySQL restores correctly leaves the catalog data as a real unknown. We eventually settled on nightly `mongodump` snapshots stored alongside our existing MySQL backups, with a quarterly restore drill covering both systems together rather than treating them as independently verified.
Monitoring needed the same treatment. Our existing alerting was built entirely around MySQL's metrics — slow query log, connection count, replication lag — and had nothing to say about MongoDB at all until we explicitly added it. A slow aggregation pipeline query on the catalog collection went unnoticed for longer than it should have during the project's first month, purely because nothing was watching for it, which was a useful (if slightly embarrassing) early lesson in not assuming existing tooling automatically extends to a new piece of infrastructure.
Search ended up depending on the same document flexibility
An unplanned benefit showed up once the storefront's search feature needed to filter by arbitrary attribute combinations — "show me all size-large items in red under fifty dollars," where "size" and "red" are themselves attributes that only apply to certain product categories. Building this against the MySQL EAV pattern we had originally rejected would have meant increasingly convoluted joins as filter combinations multiplied. Against the MongoDB documents, the same query is a single `$match` stage checking nested attribute paths, and performance held up well because the fields being filtered on were already indexed for the storefront's normal browsing queries. This was not something we had planned around, but it turned into one of the stronger arguments, in hindsight, for having made the switch.
Team skill investment paid off beyond this one project
The steepest part of adopting MongoDB was not the database itself but getting the team comfortable enough with a genuinely different query paradigm to use it confidently rather than fearfully. We ran two internal workshops specifically on aggregation pipeline patterns, using real queries from this project as the worked examples, and that investment has already paid off on a second, unrelated project that needed a similarly flexible content model for a client's configurable landing page builder. Having a team that is not intimidated by a document database, rather than reflexively reaching for MySQL out of familiarity alone, has turned out to be a durable asset beyond the original catalog project that prompted us to learn it.
How we explain the split to a client's own engineering hires
More than once, a client has later hired their own in-house developer who inherits a codebase running two different databases and reasonably asks why. We have started writing a short internal architecture note for exactly this situation, explaining not just what each database holds but why, with the specific catalog examples that motivated the decision rather than an abstract justification. A new hire reading "here is the actual attribute-variability problem that made a single MySQL table impractical" onboards onto the reasoning much faster than one reading a generic "MongoDB is good for flexible schemas" statement with no connection to the system in front of them.
Where we draw the line for future projects
Since this project, we have turned the underlying question into something closer to a decision framework we apply early in any new engagement: does the data's shape vary enough, and often enough, that a fixed relational schema would require either frequent migrations or an EAV-style escape hatch? If yes, and if the variable data is not itself the thing requiring strong transactional guarantees, a document store for that specific slice of the system is worth seriously considering. If the honest answer is "it varies a little, but not that much," we now push back on our own instinct to reach for something flexible just because it feels more future-proof, since the operational and reporting costs we described above are real and ongoing, not a one-time setup tax that disappears once the initial migration is done.