<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Cynthiaazhang on Medium]]></title>
        <description><![CDATA[Stories by Cynthiaazhang on Medium]]></description>
        <link>https://medium.com/@cynthiaazhang07?source=rss-8e501823a62f------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*IP5Bofr7Vj31ccts</url>
            <title>Stories by Cynthiaazhang on Medium</title>
            <link>https://medium.com/@cynthiaazhang07?source=rss-8e501823a62f------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 16:01:54 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@cynthiaazhang07/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Inside a gym chain’s data: 120 branches, one churn problem, and a warehouse built to catch it early]]></title>
            <link>https://medium.com/@cynthiaazhang07/inside-a-gym-chains-data-120-branches-one-churn-problem-and-a-warehouse-built-to-catch-it-early-5b76de3394ec?source=rss-8e501823a62f------2</link>
            <guid isPermaLink="false">https://medium.com/p/5b76de3394ec</guid>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[customer-retention]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Cynthiaazhang]]></dc:creator>
            <pubDate>Sat, 07 Nov 2026 11:14:13 GMT</pubDate>
            <atom:updated>2026-11-08T12:47:09.916Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BTJVdP0Mfpe4s1hXVxz78Q.jpeg" /></figure><p><em>Notes from a data warehouse and BI design project for FIT HUB Indonesia: members leave a trail before they cancel, and the design below is meant to read it.</em></p><h3>The member who stopped showing up</h3><p>A gym member rarely cancels on the day the contract ends. The decision starts earlier, when visits thin out and the membership begins to feel like money spent on nothing. By the renewal date, the gym is looking at a lost member instead of a warning.</p><p>FIT HUB Indonesia has more than 120 branches and grows through digital marketing and influencer collaborations. That kind of growth brings members in quickly. It also hides a risk: if members leave about as fast as they join, revenue never settles, and nobody at head office sees it until the numbers are already bad.</p><p>This post walks through a data warehouse and business intelligence (BI) design built to catch that problem early. It started as a course project, so one thing needs saying up front. The design is a proposal. We had no access to FIT HUB’s internal databases, which means every table, column, and source system below is conceptual, and every business insight is a hypothesis that real data would have to confirm.</p><p>The post runs in seven parts, from the churn problem to the dashboards, then closes with the case for a warehouse and the weak spots we found on review.</p><h3>Part 1: Churn is the problem, and it is hard to see</h3><p>Churn means a member who cancels or does not renew when the contract ends. One study shows how bad it can get. Mu’ti and Prasetyaningrum (2025) analyzed 1,287 membership records from EightGym Indonesia, covering July 2024 to April 2025, and found that 66.36% of members churned while only 33.64% stayed active. Five variables separated the two groups: how often members visited, how long they had subscribed, which membership category they held, whether they used a promotion, and whether they used a personal trainer.</p><p>That is one gym’s data, so it should not be read as an industry average. Still, it points at what the FIT HUB project cares about. Behavior and transaction history tell you who is likely to leave.</p><p>We framed four business problems for FIT HUB.</p><p>First, service varies between branches. Members can train at any branch, so a crowded evening session or a full class at one location can sour them on the whole membership and lower the chance they renew.</p><p>Second, a drop in engagement goes unnoticed. Santosa and colleagues (2025) studied FitHub users and found that perceived value shapes how members engage. A member who comes less often is probably seeing less value, but the evidence sits in the wrong place. Every QR check-in is stored in an operational database (OLTP), which is built to process today’s transactions and not to analyze years of attendance (Kimball &amp; Ross, 2013). Nobody can easily ask it which active members have gone quiet.</p><p>Third, class booking is unreliable. A class shows as fully booked while the studio is half empty, because members reserve early and then skip without cancelling. Other members lose a seat, and management cannot tell which classes and instructors are popular.</p><p>Fourth, the Workout Tracker in the mobile app gives thin progress feedback. Rumi and Sari (2026) found that unstable parameters and weak visualization of personal progress reduce its usefulness. This problem made the list, but the final design does not solve it. The limits section comes back to that.</p><h3>Part 2: What management needs</h3><p>The problems turn into four business drivers. The first is retention: management wants to see attendance falling well before a contract ends, while there is still time to act. The second is branch operations, meaning visitors, equipment, and class schedules spread according to the historical crowding of each location. The third is one integrated store that combines app transactions, payments, and branch operations, so that expansion decisions rest on the same data. The fourth is a move from descriptive reporting to decision support. A dashboard that shows one retention number per month is not enough. Managers need to compare retention curves across cohorts, find branches with low renewal rates, and test whether visit frequency relates to renewal, all in one consistent layer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UzoRXABwGKXXW7MXrE6SIg.jpeg" /></figure><p>From the drivers come four requirements for the data model. It must group members by registration month and package length (1, 3, 6, or 12 months) to compare retention curves and find the branches with the lowest renewal rates. It must join subscription history to daily check-in logs, so that visits per month can be set against the chance of renewing. It must add up visits by hour, by weekday versus weekend, and by month, to guide decisions about equipment and branch quotas. And it must track all-access members by comparing the branch where they registered with the branch where they train, while matching class reservations against actual attendance by class, schedule, and instructor.</p><h3>Part 3: Four numbers worth watching</h3><p>The design measures four groups of key performance indicators (KPIs), each tied to a driver.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*J93UkrLI3oi4vz7lkXzfKA.jpeg" /></figure><p>Retention comes first. The churn rate is the number of members who left during a period divided by the active members at the start of that period, times 100. The renewal rate is the number of members who renewed divided by the contracts that ended in the period, times 100. Both are viewed by registration cohort, by package, and by branch.</p><p>Engagement is second. Visit frequency is the average number of QR check-ins per member per week. The dormant ratio is the share of active members who have not checked in for 14 days or more. These members are still paying, which is exactly why the group is worth watching.</p><p>Branch capacity is third. Club utilization is the number of check-ins in a given hour divided by the maximum capacity of the branch, as a percentage. A branch that is packed at 6 p.m. and empty at noon has a different problem from one that stays moderately busy all day.</p><p>Class performance is last. The no-show rate is the share of bookings where the member neither attended nor cancelled. Class occupancy compares attendance with the seats available. Together they separate classes that are popular from classes that only look full.</p><h3>Part 4: Five sources, five layers</h3><p>The warehouse pulls from five operational sources. The membership system holds member profiles, package types, contract dates and status, renewal history, and the branch where each member registered. The payment system holds amounts, discounts, voucher codes, and payment status. The QR check-in log holds the member ID, branch, date, and time of every scan. The class reservation system holds the booking ID, class schedule and category, instructor, and a booking status of booked, attended, no-show, cancelled, or waitlist. Branch master data from the central ERP holds branch names, regions, capacity, operating hours, studio counts, and instructors.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4JlRAojMzufEHl3UbMtExQ.jpeg" /></figure><p>Data flows in one direction through five layers: the operational sources, a staging and ETL layer, the warehouse, a semantic layer, and the BI dashboards. One direction means every figure on a dashboard can be traced back to a source system.</p><p>The staging layer extracts data from each source, removes duplicates, repairs inconsistent dates, standardizes member and branch codes, and calculates the renewal, churn, and no-show flags. It loads on a schedule, for example daily in the early morning. The warehouse stores the result as fact tables and dimension tables. The semantic layer defines measures and hierarchies, such as day, month, and quarter, or branch, city, and region, so a KPI formula means the same thing on every dashboard. The dashboards sit on top.</p><h3>Part 5: The data model</h3><p>Grain comes first, because everything else depends on it. Grain is what one row in a fact table stands for. It should be as detailed as possible, since detail can be added up later but cannot be split back out (Kimball &amp; Ross, 2013). Three business processes produce the KPIs, so the model has three fact tables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U-LX684uvrwJ3XS-bs5oIQ.jpeg" /></figure><p>Fact_Membership has one row per membership contract. Fact_Checkin has one row per QR scan, with time recorded by the hour. Fact_Class_Booking has one row per seat reservation for one class session.</p><p>Eight dimension tables describe them. Dim_Member holds the profile and the cohort month. Dim_Branch holds the location hierarchy, capacity, operating hours, and studio count. Dim_Date holds calendar fields, including weekend and holiday flags. Dim_Time holds the hour of the day and whether it counts as peak. Dim_Package holds duration, price, access type, and whether personal training is included. Dim_Promo holds promotion details, with a special row for contracts that used none. Dim_Class and Dim_Instructor describe the class and the person teaching it.</p><p>Every dimension uses a surrogate key as its primary key and keeps the source system’s ID as a natural key. Member status and package change over time, so they are handled as slowly changing dimensions of type 2, which keeps one row per version and preserves history.</p><p>The fact tables carry the measurable parts. Fact_Membership stores package price, discount, amount paid, the number of check-ins during the contract, and flags for renewal, churn, and personal trainer use. Fact_Checkin holds a check-in count that is always 1, so rows can simply be summed, plus a visit duration and a cross-branch flag that equals 1 when the member trained somewhere other than the registration branch. Fact_Class_Booking holds flags for attended, no-show, cancelled, and waitlist.</p><p>Dim_Member, Dim_Branch, and Dim_Date are shared by all three fact tables, and the sharing is the point. Because the same member and the same branch appear in all three, a manager can set a member’s attendance next to the same member’s renewal. Three star schemas that share dimensions form what is called a fact constellation, or galaxy schema.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*df9d-iSli4wUG4th4P0uiQ.png" /></figure><p>Why stars and not a snowflake? There are five reasons. Queries need fewer joins, which matters when dashboards add up millions of check-in rows from more than 120 branches. A flat structure is easier for managers and for tools such as Power BI or Looker Studio. Hierarchies like branch, city, province, and region have few attributes, so splitting them into separate tables gains little. Normalizing saves little storage because dimensions are tiny next to facts. And the shared dimensions are what make cross-process analysis possible.</p><p>The KPIs map straight onto the facts. Churn and renewal come from Fact_Membership, by summing the flags and dividing by the number of contracts. Visit frequency and the dormant ratio come from Fact_Checkin. Utilization comes from Fact_Checkin and the capacity stored in Dim_Branch. No-show and occupancy come from Fact_Class_Booking and Dim_Class.</p><h3>Part 6: From raw files to dashboard</h3><p>ETL stands for extract, transform, load. The design follows a medallion architecture, which sorts the work into three layers named after medals.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*n-Gp6ipjD2N_hTDCZFXmcQ.jpeg" /></figure><p>The bronze layer holds raw data exactly as it arrived. Nothing is cleaned here, so nothing is lost before it can be checked. Data is extracted in a daily batch in the early morning, which keeps the load off members scanning at the door.</p><p>The silver layer makes the data clean and consistent. Duplicates go, empty values are examined, and dates and times are fixed. Member codes, branch codes, package categories, and statuses from different systems are standardized. If one member appears under two code formats in the membership system and the QR log, standardization makes sure both records point to the same person. Records that fail validation go to an error log instead of moving on.</p><p>The gold layer builds the three fact tables and eight dimension tables, calculates the flags, and prepares summary tables for each KPI. Four dashboards read from there. Retention shows cohort curves, renewal, and churn. Engagement shows visit frequency and the dormant ratio. Branch capacity shows utilization and peak-hour density. Class analysis shows occupancy and no-shows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yBpJoAkJu8ynRLyRzMt7qQ.jpeg" /></figure><p>Quality checks run between the layers. Completeness checks confirm that IDs and dates are not empty. Duplicate checks make sure no check-in is recorded twice. Consistency checks confirm that codes and statuses match across sources, and referential integrity checks confirm that every foreign key in a fact table has a matching dimension row. Business rule checks verify that the renewal, churn, attended, and no-show flags follow their definitions. Row counts are also reconciled between the source, the staging area, and the warehouse.</p><h3>Part 7: What the warehouse could show</h3><p>Everything in this part is a hypothesis. It describes the kind of finding the design can produce, not a result from FIT HUB data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lM8zkECYi4GgUMSnmogfxw.jpeg" /></figure><p>On retention, comparing curves by registration month might show whether cohorts recruited through digital campaigns or influencers stay for a shorter time than regular cohorts. One-month packages might churn far more than six-month or twelve-month ones. Ranking branches by renewal rate would show where to start. Comparing members who used promotions or personal trainers with those who did not would test whether the EightGym predictors hold for FIT HUB.</p><p>On engagement, plotting monthly check-ins against the renewal flag might reveal the visit frequency typical of members who renew. The dormant ratio, split by branch and by package, would size the at-risk group. Visit patterns across a contract might show when attendance usually starts to fall, for example after the first month.</p><p>On capacity, aggregating by hour and date should reveal the busiest hours, the gap between weekdays and weekends, and monthly trends. Some branches may exceed capacity at peak hours while others stay far below it. The cross-branch flag would show which branches host many all-access members registered elsewhere, so a branch’s real load can be compared with its registered members.</p><p>On classes, combining class, time, and instructor might expose sessions that are fully booked but poorly attended. Comparing reservations and attendance with seat capacity separates truly popular classes from ones that only look full, and the waitlist flag points to classes with real unmet demand.</p><p>If the findings hold, the recommendations follow, and each depends on validation with real data. For management, renewal rate and a ceiling on the dormant ratio could become targets for branch managers alongside new sign-ups. If some campaign cohorts prove to leave quickly, part of the acquisition budget could move to retention. Capacity data could inform decisions on equipment and new branch locations, and retention results by package and promotion could guide pricing.</p><p>At branch level, members who have been away for 14 days or more could be contacted through app notifications, class invitations, or an orientation with a personal trainer before their contracts end. Crowded branches could add staff, rearrange equipment, or offer incentives for off-peak visits. Classes with high no-show rates could get cancellation rules, automatic reminders, or limited overbooking that uses the waitlist. Low-occupancy classes could be rescheduled or replaced, and classes with long waitlists could get extra sessions. Branches with low renewal rates could be reviewed for crowding, class availability, and facility condition.</p><h3>Why not just report from the source systems?</h3><p>Reports can be pulled straight from operational systems, and many businesses do. The trouble is that those systems are built to record today’s transactions, not to compare years of history across systems (Kimball &amp; Ross, 2013). A warehouse fixes several problems at once.</p><p>It joins membership, payment, check-in, and booking data, so attendance can be set against renewal. It gives every branch the same definition of a member and of each KPI. It keeps history, where operational systems overwrite, and type 2 dimensions record when a member’s status or package changed. It moves heavy analytical queries away from the systems that handle QR scans and bookings. And it flags declining engagement before a contract ends, while operational reports mostly show members who already left. Adding a branch or a data source means extending the ETL process and the dimension tables instead of rebuilding reports. Wixom and Watson (2010) describe this shift from reporting to decision support as the point of BI.</p><h3>Limits and weak spots</h3><p>The biggest limit is that the design has never touched real data. Table structures and column names may differ from FIT HUB’s actual systems. The EightGym study covers one gym’s records, so FIT HUB members may behave differently. The 14-day dormant threshold and the daily refresh are assumptions, and both need calibrating.</p><p>The model also leaves things out. The Workout Tracker is not modelled, so the fourth business problem stays open. Utilization counts check-ins and has no check-out times, which means the number of people inside a branch at the same moment is only an estimate. The churn flag depends on a business rule such as not renewing after the contract ends, and a grace period for renewal would change every retention number. A link between visits and renewal does not prove that visiting causes renewal, because things like a move to another city never show up in the data.</p><p>The practical costs are real too. A project like this needs infrastructure, ETL and BI tools, and people such as data engineers and BI analysts. We did not estimate the price, and the return can only be judged after implementation. Integration depends on member and branch identifiers that match across systems, on a payment gateway that stays available, and on mobile app updates that do not silently change the data structure and break the ETL.</p><p>Privacy is the most serious operational risk. The warehouse holds member profiles, payment history, and visit patterns, all of which are personal data under Law Number 27 of 2022 on Personal Data Protection. Suggested protections are role-based access, pseudonymization on dashboards, keeping only the data that is needed, and member consent. Daily refreshes also mean dashboards are not real time, which rules them out for decisions that need minute-by-minute information.</p><p>A second look at the model turned up weaker spots that the first draft missed. Members who never scan leave no rows in the check-in table, so the dormant ratio cannot be calculated from that table alone. A daily snapshot of every active member, with the days since their last check-in, would fix it. Class occupancy divides attendance by seats, but a booking row does not say which session it belongs to, so a session identifier is needed. The plan to truncate and reload tables every night would erase the history that type 2 dimensions exist to keep, so facts should load incrementally and dimensions should merge changes. The check-in table has a visit duration column, but the sources record no check-out, and where that number comes from is an open question. Finally, renewal and churn are two flags for what are really three states, renewed, churned, and not ended yet, and a contract that is still running should count as neither.</p><p><em>By Cynthia Tipani Tio &amp; Juan Davin Setiawan, Universitas Bunda Mulia. This article is a plain-language version of a project report on</em> <em>designing a data warehouse and BI system for FIT HUB Indonesia. The design is a proposal and has not been tested with FIT HUB data. Figures come from the cited studies, not from FIT HUB’s databases. Drafting was assisted by an AI assistant (Claude, Anthropic). Contains AI-generated images from Gemini.</em></p><h3>References</h3><p>Kimball, R., &amp; Ross, M. (2013). <em>The data warehouse toolkit: The definitive guide to dimensional modeling</em> (3rd ed.). John Wiley &amp; Sons.</p><p>Mu’ti, D. L., &amp; Prasetyaningrum, P. T. (2025). Comparative analysis of classification algorithms for predicting membership churn in fitness centers: Case study and predictive modeling at EightGym Indonesia. <em>Journal of Information Systems and Informatics, 7</em>(2). <a href="https://doi.org/10.51519/journalisi.v7i2.1120">https://doi.org/10.51519/journalisi.v7i2.1120</a></p><p>Republic of Indonesia. (2022). Law Number 27 of 2022 on Personal Data Protection.</p><p>Rumi, K. S., &amp; Sari, A. R. (2026). Evaluating user experience of the Workout Tracker feature in the FIT HUB mobile fitness application using the User Experience Questionnaire and usability testing. <em>Jurnal Informatika dan Teknologi Komputer (JITEK), 6</em>(1), 1–12. <a href="https://doi.org/10.55606/jitek.v6i1.10889">https://doi.org/10.55606/jitek.v6i1.10889</a></p><p>Santosa, D. P., Alam, B. M., Widjojo, H., &amp; Istijanto. (2025). Pengaruh perceived value terhadap customer engagement behavior di pusat kebugaran FitHub. <em>Kajian Branding Indonesia, 7</em>(1), 61–81. <a href="https://doi.org/10.21632/kbi.7.1.61-81">https://doi.org/10.21632/kbi.7.1.61-81</a></p><p>Wixom, B. H., &amp; Watson, H. J. (2010). The BI-based organization. <em>International Journal of Business Intelligence Research, 1</em>(1), 13–28. <a href="https://doi.org/10.4018/jbir.2010071702">https://doi.org/10.4018/jbir.2010071702</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5b76de3394ec" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Inside a bank’s database: 5.8 million rows and nine broken rules]]></title>
            <link>https://medium.com/@cynthiaazhang07/inside-a-banks-database-5-8-million-rows-and-nine-broken-rules-8577c0da9d9b?source=rss-8e501823a62f------2</link>
            <guid isPermaLink="false">https://medium.com/p/8577c0da9d9b</guid>
            <category><![CDATA[fintech]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[system-design-interview]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Cynthiaazhang]]></dc:creator>
            <pubDate>Wed, 04 Nov 2026 11:50:03 GMT</pubDate>
            <atom:updated>2026-11-10T15:25:32.841Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QLrdrX901bJjqvrt6rR9RQ.jpeg" /></figure><p><em>Notes from a database design project for a retail bank: the data was a mess, the design had to change, and every fix came with a cost.</em></p><h3>One tap on Transfer</h3><p>When you tap Transfer in a banking app, the system has a few milliseconds to decide whether you have enough money. That answer comes from the bank’s database, where it keeps every customer, account, and transaction.</p><p>This project started with 10 CSV files from a retail bank in India. The details give it away: branch codes follow the IFSC format and one of the payment channels is UPI. The files hold 5,868,950 rows covering 60,000 customers, 95,000 accounts, 65,000 cards, 5 million transactions, 22,000 loans, and 25,000 complaint tickets, with transactions running from January 2018 to June 2026.</p><p>The job was to redesign the database. A first look showed the data was badly broken, and the ways it broke explain why a bank’s database needs strict rules.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AIlnJZyl1Qy7hKghWiVHmw.png" /></figure><p>The article runs in seven parts, from the problems in the data to the trade-offs at the end.</p><h3>Part 1: Data that can’t be true</h3><p>Everything sat in plain CSV files. Nothing stopped an impossible value from being written, so the files took whatever came in. Of the 10 business rules checked, nine were broken.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*byVDI3s_aCGnvzhNVGrgqA.png" /></figure><p>The worst case is in the card table. 64,998 of 65,000 cards belong to a different customer than the account they’re linked to. The cause is simple: the owner was stored twice, once on the card and once on the account, and nothing kept the two in sync.</p><p>The dates are a mess too. 930,778 card transactions happened before the card was issued and another 717,101 after it expired, which adds up to 54.93% of all 3 million card transactions. On the loan side, 280,287 installments were paid before the loan started and 131,750 after the term had ended.</p><p>There are also 16,025 credit cards with a limit of zero and 16,041 debit cards that somehow have a credit limit. And every one of the 4,949 tickets marked <em>Open</em> or <em>Escalated</em> already has a close date.</p><p>Some findings aren’t errors, but they still shape the design. 7,903 customers joined before they turned 18, which calls for guardian rules. 12,360 customers have no account at all. And 90.16% of accounts were opened at a branch in a different state from where the customer lives, a number that comes back in Part 5.</p><h3>How big is it?</h3><p>About 85% of the rows sit in two transaction tables. Each year adds roughly 235,000 account transactions and 353,000 card transactions. That’s small for a bank, but the tables keep growing with nothing splitting them up by period.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lyiYyb4Z14jN3ritNGpXzQ.png" /></figure><h3>What’s at risk</h3><p>2,221 of the 22,000 loans (10.10%) ended in default or were written off. 12.01% of installments came in late, and 14,954 card transactions (0.50%) were flagged as fraud. The same files hold names, birth dates, phone numbers, income, and credit scores, which PCI DSS and data protection law require the bank to protect.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-V2UuAIQKDa7zUGpG4ezYA.png" /></figure><p>That leaves the new design with five jobs. The system has to enforce business rules on its own, and the same fact can’t live in two places anymore. The transaction tables need to be split by period. The data has to work across 150 branches in 12 states, even though 47.76% of customers with an account hold accounts in more than one region. Last, security has to meet banking standards.</p><h3>Part 2: What the system has to do</h3><p>The staff data shows who uses the system: 673 tellers, 443 customer service agents, 256 relationship managers, 242 loan officers, 97 branch managers, and 89 compliance officers. Customers come in through six channels: the mobile app, online banking, ATMs, card terminals, UPI, and branch counters. Each channel handles about 333,000 transactions, so none of them can be treated as an afterthought.</p><h3>Functional requirements</h3><p>The system needs 13 features:</p><ol><li>Register customers, verify their identity (KYC), and reject duplicate phone numbers or emails.</li><li>Open five account types (savings, current, salary, fixed deposit, and NRI) and track whether each is active, dormant, or closed.</li><li>Record six transaction types from six channels, updating the balance in the same step.</li><li>Issue debit cards and <em>Classic</em>, <em>Gold</em>, and <em>Platinum</em> credit cards, then handle blocking and expiry.</li><li>Approve a card transaction only when the card is active, still valid, and under its limit.</li><li>Give every card swipe a fraud risk score, and store the fraud label once a case has been investigated.</li><li>Record six loan types with terms of 12 to 240 months and interest rates of 6.5% to 16.5%.</li><li>Record each installment with its principal, interest, and a late flag.</li><li>Track bad loans and move them to defaulted or written off.</li><li>Keep branch and staff records up to date.</li><li>Handle complaint tickets for 10 issue types, each with an assigned agent, an SLA, and a satisfaction score from 1 to 5.</li><li>Feed dashboards and regulator reports from the data warehouse.</li><li>Keep an audit trail of every change to money and every time someone opens personal data.</li></ol><h3>Non-functional requirements</h3><p>These set how fast and how resilient the system has to be. Every target has a number so it can be tested.</p><p>Uptime and speed come first. The system has to be up at least 99.99% of the year, which allows no more than 52.6 minutes of downtime. 95% of transactions must finish in 200 ms or less, and 95% of card authorizations in 100 ms or less with the fraud check included. It also has to handle 10 times today’s volume without changes to its structure.</p><p>On data quality, zero records may break the rules after migration, and balances must match the transaction log exactly at the end of every day. If a server fails, no data can be lost. In industry terms, the recovery point objective (RPO), meaning how much data you can afford to lose, is zero. If a whole site fails, service has to be back within 15 minutes; that’s the recovery time objective (RTO), meaning how long service can stay down. Both come up again in Part 6.</p><p>For security, data is encrypted with TLS 1.3 in transit and AES-256 at rest, and every access to personal data is written to the audit log. Reports may lag live data by 5 minutes at most, and the database structure has to be changeable without taking the service down.</p><h3>Part 3: The data family tree (ERD)</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iQvhJ_x4maaEfvsCHWrOAg.jpeg" /></figure><p>An ERD (entity relationship diagram) shows the tables in a database and how they link to each other, a bit like a family tree. The new design has 13 tables. Ten come from the original files, and three are new lookup tables: STATE, CITY, and MERCHANT_CATEGORY.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Gn3KrOEMxe4aRbV1JsL-ng.png" /></figure><p>In this diagram, a line ending in a crow’s foot means many rows, a bar means exactly one, and a small circle means the link can be empty. So one customer can have zero or many accounts, while every account has exactly one owner. These rules are called cardinality, and each one was checked against the actual data:</p><ul><li>A customer can have zero to many accounts. The average is 1.99, the maximum is 9, and 12,360 customers have none.</li><li>An account can have zero to many cards. The 65,000 cards are spread across 50,816 accounts.</li><li>A customer can have zero to many loans, and the 22,000 loans belong to 19,535 customers.</li><li>A loan always has at least one installment. The minimum is 9, the average 27.3, and the maximum 50.</li><li>A customer can open zero to many tickets; 25,000 tickets came from 23,431 customers.</li><li>A branch always has at least one staff member, and all 150 branches do, with 1,800 people in total.</li></ul><h3>Four structural changes</h3><p>Normalization means tidying tables so each fact is stored in exactly one place. Here it led to four changes:</p><ol><li>The owner column on the card table is gone, since a card’s owner can be read from its account. The 64,998 mismatched cards can’t happen again.</li><li>Cities and states moved into their own tables. The state table also stores a region code, which Part 5 uses to split the data.</li><li>Merchant categories became an official list that account transactions are allowed to leave empty. An interest payment no longer has to carry a shopping category.</li><li>Complaint tickets got an assigned-agent column, so handling time can be measured.</li></ol><h3>Part 4: Architecture</h3><p>The architecture describes which components run the database and how a request moves between them. This design has six layers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/945/1*_j581Tg4uhmZtd9n5nPagA.jpeg" /></figure><p>The top layer holds the six channels that customers and staff use, and none of them can reach the database directly. Every request goes through the API gateway first. The gateway checks the user’s identity with OAuth 2.0 and encrypts the connection with TLS 1.3, then hands the request to the account, card, loan, or ticket service.</p><p>Below that, PgBouncer queues connections so a spike from six channels doesn’t overload the server. HAProxy then splits the traffic: writes go to the primary server and reads go to a replica.</p><p>At the center is the OLTP (online transaction processing) cluster that runs the day’s transactions. Only the primary node can write. Every transaction is copied to a synchronous standby in another zone before it counts as done, and a read replica serves reports. Patroni and three etcd nodes watch the cluster; if the primary dies, they pick a replacement by majority vote.</p><p>Debezium reads every change from the database’s write-ahead log (WAL) and sends it through Kafka to two places, a fraud detector that works in near real time and a data warehouse for reporting. Keeping the two workloads apart means a report that scans millions of rows won’t slow down someone’s payment.</p><p>The last layer handles recovery and security. It holds the WAL archive and backups that can’t be altered (WORM), copies at another site, encryption key storage (HSM/KMS), and audit log monitoring (SIEM).</p><h3>Partitioning the transaction tables</h3><p>The two transaction tables hold 85.2% of all rows, so both are split by month, a technique called partitioning. One partition holds about 19,600 account transactions or 29,400 card transactions. Old data can be archived by detaching a partition instead of deleting rows one by one.</p><p>Account and card transactions are split by month and indexed by account or card number plus date, with an extra index on card transactions flagged as fraud. Installments are split by year, and each loan number and payment date pair has to be unique. The customer and ticket tables stay whole. Customers get unique indexes on phone and email, while tickets get an extra index for the ones still open.</p><p>Money is stored as an exact decimal, NUMERIC(18,2), so no rounding errors creep in.</p><h3>Part 5: Splitting, copying, and placing the data</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yx5MAL5trSu9q0bzb7Z7RQ.jpeg" /></figure><p>The bank has 150 branches across 12 states. Put all of its data on one server and a single outage can take every branch offline at once. So the design uses a distributed database, where the data is split up and stored at several locations called sites.</p><h3>Five sites</h3><p>The 12 states are grouped into four regional sites by geography, plus a head office (S0) for shared data and analytics. S1 North covers Delhi, Punjab, Rajasthan, and Uttar Pradesh with 40 branches. S2 West–Central has Maharashtra, Gujarat, and Madhya Pradesh with 59 branches. S3 South covers Karnataka, Kerala, Tamil Nadu, and Telangana with 36 branches, and S4 East is West Bengal alone, with 15.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sTZwhUToWddiRjIuUE7OnQ.png" /></figure><p>S2 carries 39.19% of account transactions, S4 only 9.90%. The split is uneven on purpose, because the goal is to keep data close to the branches that use it. Each site’s servers are then sized to its load.</p><h3>Fragmentation</h3><p>Horizontal fragmentation starts by grouping the branch table by region. Accounts, cards, transactions, loans, installments, and staff then follow their branch, which is called derived fragmentation. A customer at a Mumbai branch is served entirely by S2.</p><p>The split has to pass three correctness checks. Every state belongs to exactly one site, so no data falls through the cracks. Put all the pieces back together and you get 95,000 accounts and 2,000,000 transactions again. And no account is stored twice, because each account belongs to a single branch.</p><p>The customer table is cut differently. Almost half of customers (47.76%) have accounts at more than one site, so the table is split vertically. The profile columns (gender, city, occupation, income, and credit score) are copied to every site because they’re used for checks and credit decisions. The identity columns (name, date of birth, phone, and email) are stored encrypted at head office only. The two halves join back together on the customer ID.</p><h3>Replication</h3><p>Within a site, copies are synchronous. A transaction only counts once the standby in another zone has saved it too, so losing one server loses no data.</p><p>Between sites, copies are asynchronous and paired: S1 with S4, S2 with S3. Each large site is paired with a small one so backup capacity isn’t wasted.</p><p>Reference data and customer profiles are written only at S0 and then pushed out to every site with a short delay (lazy replication). They’re at most 60,000 rows and rarely change, so a few seconds of lag does no harm.</p><p>If the network splits, two servers can each decide they’re the primary. This is called split-brain, and the fix is simple: a failover only goes ahead if at least 2 of the 3 etcd nodes agree.</p><h3>Allocation</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/975/1*ELFDKuYa4j8WmXeDZYrWyQ.jpeg" /></figure><p>Head office (S0) holds the primary copy of the reference data, meaning states, cities, branches, and merchant categories, along with customer profiles. Every regional site keeps a read copy of both. Customer identity data and complaint tickets live only at S0, with a backup at S3.</p><p>Each region’s transactions have their primary copy at home and a backup at the partner site: S1 is backed up at S4, S2 at S3, S3 at S2, and S4 at S1. The data warehouse, which combines everything, sits at S0.</p><p>Two cases still involve more than one site. When a new customer opens an account, the system waits until their profile has been copied to the target site. The full view of a customer with accounts in several regions comes from the data warehouse at S0, which keeps that work off the transaction path.</p><h3>Part 6: Concurrency, recovery, and security</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*py0BbMawRRWGtoRFWXBPWA.jpeg" /></figure><h3>Concurrency</h3><p>Say two people withdraw ₹1,000 from a joint account holding ₹1,500 at the same moment. Without a safeguard, both withdrawals go through and the balance goes negative. Handling transactions that run at the same time is called concurrency control.</p><p>PostgreSQL uses MVCC (multiversion concurrency control): each transaction reads its own version of the data, so reads never block writes. How strict the protection gets depends on the type of transaction.</p><p>Deposits, withdrawals, and transfers within a site only reduce a balance if it covers the amount (balance &gt;= x), inside the same transaction that records the payment. That rules out negative balances and lost updates. A transfer between two accounts locks both in a fixed order, lowest account number first, so two transfers can never end up waiting on each other forever (a deadlock).</p><p>Card authorizations take money from the limit only when there’s room, and each request carries a unique key, which stops both overspending and double charges. An installment can’t be recorded twice because every loan and payment date pair must be unique. Profile updates check a version number, so one person’s change can’t silently overwrite another’s. Reports read a point-in-time copy on a replica or in the data warehouse, so their numbers always add up.</p><p>Loan approval needs the strictest setting. Two applications arriving together can each pass the exposure limit on their own and still exceed it combined. This is called <em>write skew</em>, and PostgreSQL’s <em>Serializable</em> mode catches it and cancels one of the two.</p><p>Transfers between sites use the saga pattern. The source site moves the money into a holding account and writes an outgoing message in one local transaction, then the destination site credits the receiver. If a step fails, a reversing transaction runs. <em>Two-phase commit</em> isn’t used because every participant can get stuck waiting if the coordinator dies. At the end of each day, the holding account has to be back at zero.</p><h3>Recovery</h3><p>Every change is written to the WAL before the data itself is saved. If a server crashes, committed transactions are replayed and unfinished ones are thrown away. Each failure scenario has its own target:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RiFw-jDIx4mcrpHs1f2cOA.png" /></figure><p>Full backups run weekly and differential backups daily, while the WAL archive ships continuously, at least every 60 seconds. Restores are tested every month and site failover is rehearsed every six months, because a backup nobody has restored hasn’t been proven to work.</p><h3>Security</h3><p>The database only accepts connections from PgBouncer on a private network, and every connection uses TLS 1.3. Disks are encrypted with AES-256. Personal data columns get a second layer of encryption with keys held in the HSM/KMS, so even database admins can’t read customer names. Card data follows PCI DSS, including tokenized card numbers, and the design is aligned with Indonesia’s OJK Regulation №11/POJK.03/2022 and Law №27 of 2022 on Personal Data Protection.</p><p>Each role gets only the access it needs. Branch staff can see only their own branch’s data, enforced with row-level security. Compliance officers can read every branch, but each access is logged.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2eR70pzIngksdKgmZoRdiQ.png" /></figure><p>No role can delete financial records. Mistakes are fixed with reversing transactions, so the audit trail stays complete.</p><h3>Part 7: Conclusion, limits, and trade-offs</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U86WXoBvaw4l8S56CrmmWQ.jpeg" /></figure><h3>Conclusion</h3><p>The bank’s core problem is that its systems don’t enforce its own business rules: nine of the ten rules checked were broken. At the data level, the 13-table ERD removes duplicate storage and moves the rules into the database itself. The architecture keeps transactions apart from analytics, copies every transaction before it counts, and lets a majority vote choose the replacement server, with 99.99% availability as the target. For distribution, transaction data stays in its branch’s region while customer identity data is kept at S0.</p><h3>Limits</h3><p>This data is very likely synthetic. Transactions are spread almost evenly across six channels and the late-payment rate sits near 12% for every loan status, which real banks rarely show. Partition sizes and capacity will need recalibrating against real data.</p><p>The files contain no card numbers, general ledger entries, or amortization schedules, so tokenization and ledger reconciliation couldn’t be tested. Every performance and recovery number is still a design target that hasn’t been load tested.</p><p>What to do with the 1,647,879 card transactions and 412,037 installments that break the rules is still undecided. They should be quarantined first and then reviewed with the business team. The site split is also based on geography alone, because the data has no access logs to measure real network costs.</p><h3>Trade-offs</h3><p>Each major decision bought something and cost something.</p><p>Synchronous copies within a site mean no data is lost when a server fails, but every transaction gets a little slower and writes stall if the standby has trouble. Asynchronous copies between sites keep distance from slowing anything down, at the risk of losing the last few seconds of data if a whole site goes down.</p><p>Splitting data by branch region keeps daily work local. The price is uneven load, with S2 carrying about four times as much as S4, and any view of a customer across regions has to go through the data warehouse. Keeping identity data at head office shrinks the attack surface and simplifies compliance, though showing a customer’s name now means a call to S0.</p><p>The saga pattern means nothing freezes when a coordinator dies. In exchange, balances are only eventually consistent, and the system needs reversal logic and daily reconciliation. <em>Serializable</em> mode makes the loan exposure limit impossible to break, but some transactions get cancelled and have to retry. Normalization stops data from contradicting itself while making reports need more joins, which the data warehouse absorbs. Monthly partitions make archiving fast, though they add 24 new partitions a year that have to be created automatically.</p><p><em>By Cynthia Tipani Tio &amp; Juan Davin Setiawan, Universitas Bunda Mulia. This article is a plain-language version of a project report on designing a distributed database for retail banking. Every number was computed directly from the 10 source files. Drafting was assisted by an AI assistant (Claude, Anthropic). Contains AI-Generated images from Gemini.</em></p><p>References: Chen (1976) on the entity-relationship model; Codd (1970) on the relational model; Elmasri &amp; Navathe (2016), <em>Fundamentals of Database Systems</em>; Kleppmann (2017), <em>Designing Data-Intensive Applications</em>; Özsu &amp; Valduriez (2020), <em>Principles of Distributed Database Systems</em>; Ports &amp; Grittner (2012) on Serializable Snapshot Isolation in PostgreSQL; PCI DSS v4.0.1 (2024); OJK Regulation №11/POJK.03/2022; Indonesia’s Law №27 of 2022 on Personal Data Protection.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8577c0da9d9b" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>