In this exercise you build the CricketPlatform — a polyglot persistence system that uses four different databases for four different components, each matched to the specific requirements of that component. You will implement: a PostgreSQL normalised schema for career statistics (complex joins and aggregations), a Redis sorted set leaderboard (real-time rank updates), a MongoDB document store for player profiles (variable-schema attributes), and a schema design for Cassandra delivery events (high write throughput time-series). For each component, you will query the data, measure performance, and document why the chosen database is correct for that specific use case.
The exercise is structured as a progressive build: implement each component independently, then connect them with a data flow that demonstrates how the databases complement each other in a real pipeline. PostgreSQL is the source of truth; Redis caches the most-queried aggregation; MongoDB stores the extended player profile; Cassandra receives high-throughput delivery event writes that would overwhelm PostgreSQL. Each component's test verifies both correctness and the specific performance characteristic that justifies the database choice.
By the end of this exercise, you will have a working multi-model data system with documented selection reasoning, performance measurements, and a clear data flow diagram showing how each database fits into the overall platform architecture. The selection reasoning document produced here is the template for database selection decisions on real projects — explicit requirements mapping, explicit trade-off acknowledgement, and a clear alternative considered section.
Analogy🏏Cricket
🏏 Think of it like cricket: This exercise runs like a proper tournament bureau's production week, and the step order is the point. Step 1 is the pitch inspection before play: you verify the ground truth — no orphaned scorecard lines, no impossible totals — because an analysis built on a corrupt book is a match played on a dangerous pitch: everything after it is invalidated. Step 2 is the specialist coaches' reports: batting summaries with rankings and form lines, each an independent, checkable piece of work using window functions over the validated data. Step 3 is the selectors' composite: batting and bowling folded into one all-rounder view via conditional aggregation — the wide wall chart built from the long book. Step 4 is the match referee's reconciliation: the chart's totals must re-add to the book's totals exactly, or something was dropped or double-counted on the way. Validate, analyse, combine, reconcile — every production pipeline plays in that order.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.
Component 1 — PostgreSQL: Career Statistics
Implement the normalised career statistics schema in PostgreSQL. Create the innings table with appropriate indexes, populate with 500,000 synthetic rows, and implement the career_stats materialised view. Verify that the complex career average query runs in under 100ms with the materialised view.
Analogy🏏Cricket
🏏 Think of it like cricket: PostgreSQL in this architecture is the official scorers' record office — the one place where every innings is entered in full, normalised detail, because career statistics demand auditable, consistent history. Proper indexes on the innings table cross-reference the archive by player and date; loading 500,000 synthetic rows stress-tests the office with decades of scorebooks, not just last week's. The career_stats materialised view is the printed career-averages almanac: rather than re-adding every innings from every book each time someone asks for a batting average, the office compiles the almanac once and hands out copies — which is exactly why the complex career-average query drops under 100ms. Just as no broadcaster computes Tendulkar's average from raw scorebooks live on air, no dashboard should aggregate half a million rows per request. Practising this component teaches the foundational pattern of the whole exercise: the relational store is the source of truth, and pre-computation is how truth becomes fast. The payoff: a verified system of record that also answers its most common question in milliseconds.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.
Component 2 — Redis: Real-Time Leaderboard
Seed the Redis leaderboard from the PostgreSQL materialised view, implement a real-time update function, and verify that leaderboard reads take under 1ms. Compare the Redis sorted set approach with re-running the PostgreSQL aggregation query to justify the Redis component.
Analogy🏏Cricket
🏏 Think of it like cricket: Redis here is the stadium's big screen, and PostgreSQL is the scorers' office behind it. Seeding the leaderboard from the materialised view is the screen operator copying the office's certified standings onto the board before the gates open — the screen never invents numbers; it displays the record office's truth. The real-time update function is the operator nudging a batter up the board the moment runs are scored (ZINCRBY on a sorted set), so 90,000 spectators glance up and read the answer in under a millisecond instead of each phoning the scorers' office and waiting for a clerk to re-total the books — which is what re-running the PostgreSQL aggregation per read would mean. The sorted set is purpose-built for exactly this: it keeps entries permanently ordered by score, so 'top ten run-scorers' is a direct read, not a computation. Practising the comparison between the two approaches is the point of the component: you measure that the big screen serves reads thousands of times cheaper, and you justify Redis with numbers rather than fashion. The payoff: sub-millisecond reads at spectator scale, with the record office still owning the truth.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.
Component 3 — MongoDB: Player Profiles
Create the MongoDB player profiles collection with schema validation and embed variable attributes that would require nullable columns or separate tables in PostgreSQL. Implement queries showing why MongoDB handles variable-schema data more naturally than additional PostgreSQL nullable columns.
Analogy🏏Cricket
🏏 Think of it like cricket: MongoDB in this architecture is the players' personal kit bags, where PostgreSQL is the standard-issue team locker. Every locker has identical compartments — perfect for the facts every player has (name, matches, runs) — but players' personal extras vary wildly: one carries endorsement contracts, another injury rehab notes, a third a T20 franchise itinerary. Forcing those into the standard locker means empty compartments (nullable columns) or an annex per quirk (extra tables); a kit bag holds whatever the player actually owns — the document model. Schema validation is the kit inspection at the door: bags may differ inside, but every bag must carry the mandatory items (required fields, correct types), so flexibility never becomes chaos. Practising the comparison queries shows why this matters: asking 'which players have a sponsorship clause' reads naturally against documents, where the relational version would join sparse side-tables or filter oceans of NULLs. The payoff: you learn to give each data shape its natural home — uniform facts in the relational locker, variable ones in validated documents — and to defend that split with working queries.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.
Component 4 — Cassandra: Delivery Events Schema
Design and create the Cassandra delivery events table for the high-throughput match event stream. Verify partition key design avoids hot-spotting, demonstrate TTL-based automatic data expiry, and implement a query that reads a specific over's deliveries efficiently using the partition key.
Analogy🏏Cricket
🏏 Think of it like cricket: Cassandra partition key design is crowd distribution at the turnstiles. A match generates a torrent of delivery events, and the partition key decides which gate (node) each event queues at. Key the events by something low-cardinality and skewed — say, just the season — and every fan converges on one gate while the other gates stand idle: a hot partition, one node melting while the cluster loafs. Key by match (and bucket long matches) and the crowd spreads evenly — each match's events file through their own gate in arrival order, which is exactly the read you want later: 'give me match 4021's deliveries, in order' hits one gate, one queue, already sorted by the clustering columns. The design question to ask before creating the table is the steward's question: what will people ask for at read time, and does that request map to one well-sized queue rather than a stadium-wide scavenger hunt?
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.