Case Study 1: Meridian Bank Complete Data Model

Full Walkthrough — From Requirements to Logical Model

Background

You are the newly hired data architect at Meridian National Bank. The bank is modernizing its core banking platform, migrating from a patchwork of legacy systems — some dating back to the 1990s — to a unified database running on IBM DB2. The legacy landscape includes:

  • A COBOL-based checking and savings system on an aging IMS database
  • A standalone loan origination system built on Microsoft Access (yes, really)
  • A credit card processing system outsourced to a third-party vendor
  • Customer data spread across all three systems with no single source of truth
  • An Excel-based fee schedule maintained by the product management team
  • Paper-based audit trails stored in file cabinets at each branch

The CIO has given you a mandate: design a unified data model that serves as the single source of truth for all customer, account, transaction, and regulatory data. The model must support the current business, accommodate planned growth (expansion to two additional states within 18 months), and satisfy regulatory requirements from the OCC, FDIC, and FinCEN.

You have six weeks. Here is how you approach it.

Week 1: Discovery and Requirements Gathering

Stakeholder Interviews

You schedule interviews with eight stakeholders across the bank:

Interview 1: Chief Operations Officer (COO)

"Our biggest pain point is customer identification. When a customer walks into a branch, the teller has to check three different systems to see all their accounts. Last month, we failed an OCC examination because we could not produce a complete customer profile within the required timeframe. I need one place to see everything about a customer."

Takeaways: CUSTOMER must be a unified entity. All accounts, regardless of type, must be linked to a single customer record. The relationship between CUSTOMER and ACCOUNT is the most critical in the model.

Interview 2: Branch Manager (Downtown Branch)

"When someone opens a joint account, we create two customer records and link them both to the account. But when one of the joint holders calls to check the balance, our system only shows the account under the 'primary' holder. The other holder sees nothing unless we do a manual lookup. Also, we have customers who are both individual account holders and authorized signers on a business account. They show up as two different customers."

Takeaways: The CUSTOMER-ACCOUNT relationship is many-to-many, not one-to-many. Joint holders, authorized signers, and beneficiaries are all types of relationships between a customer and an account. We need a junction table (ACCOUNT_HOLDER) with a HOLDER_TYPE attribute. Individual and business customers are subtypes of a common CUSTOMER supertype.

Interview 3: Loan Officer

"I need to see the applicant's complete financial picture: all deposits, all existing loans, credit score history, and employment verification documents. For secured loans, I track the collateral — sometimes one property secures two different loans (a mortgage and a HELOC). The approval workflow goes through stages: submitted, under review, credit check, appraisal, decision, and funding. Each stage has different people involved."

Takeaways: LOAN extends ACCOUNT (every loan is an account). COLLATERAL is a separate entity with a one-to-many or many-to-many relationship to LOAN. We need a CUSTOMER_DOCUMENT entity for verification documents. The loan workflow suggests a LOAN_APPLICATION entity with stage tracking — but that is an extension for a later phase. For now, we model the core LOAN attributes.

Interview 4: Compliance Officer

"Federal law requires Currency Transaction Reports for any cash transaction over $10,000. We also file Suspicious Activity Reports when we detect patterns like structuring (breaking up large transactions to avoid reporting thresholds). I need every transaction to have a channel indicator (branch, ATM, online), and I need to flag transactions for review. Our BSA officer spends 30% of her time manually correlating data across systems."

Takeaways: TRANSACTION needs a CHANNEL attribute. We need ALERT and CTR_FILING entities in the regulatory domain. The BSA/AML requirements drive the need for a unified transaction history across all account types.

Interview 5: Product Manager

"We offer 14 different account products today and plan to add 5 more in the next year. Each product has a different fee structure and interest rate schedule. Rates change quarterly. Fees change less often but must be tracked historically for dispute resolution. Some products are only available at certain branches."

Takeaways: PRODUCT is a first-class entity. FEE_SCHEDULE and INTEREST_RATE_SCHEDULE need effective dating. BRANCH_PRODUCT is a junction table for the M:N relationship between BRANCH and PRODUCT.

Interview 6: IT Security Manager

"We are rolling out online banking. Each customer needs a login profile, but not all customers will enroll. We need to track every login attempt — successful or failed — for fraud detection. Two-factor authentication must be supported."

Takeaways: ONLINE_PROFILE is a 1:1 optional extension of CUSTOMER. LOGIN_HISTORY is a high-volume entity linked to CUSTOMER.

Interview 7: Internal Audit Director

"Every change to customer data, account data, and loan data must be logged with who made the change, when, and the before/after values. This is non-negotiable. We had a finding last year where a teller changed a customer's address and there was no trail. The regulators were not happy."

Takeaways: AUDIT_LOG is a required entity. Shadow tables for CUSTOMER and ACCOUNT may also be needed for point-in-time reconstruction.

Interview 8: CFO

"I need quarterly Call Reports for the FDIC. These reports aggregate data across all accounts: total deposits by type, total loans outstanding by category, delinquency rates, capital adequacy ratios. Right now it takes three people two weeks to compile a Call Report because the data is in different systems with different definitions of 'deposit' and 'loan.'"

Takeaways: REGULATORY_REPORT is a tracking entity. More importantly, the data model must use consistent definitions — ACCOUNT_TYPE must clearly distinguish deposits from loans, and every account must have an unambiguous classification.

Document Analysis

You collect and analyze:

  • Account application forms (checking, savings, CD, loan): These confirm the attributes needed for each account type and reveal the subtype-specific fields.
  • Monthly statement templates: These show which data appears on customer-facing documents — and therefore which JOINs will be most frequent.
  • OCC examination workpapers: These list the data elements regulators expect to see in a customer profile.
  • The existing IMS database schema: This reveals entities like CUSTOMER, ACCOUNT, and TRANSACTION, but with embedded addresses, no audit trail, and hardcoded account type logic.

Week 2-3: Entity Identification and Relationship Mapping

From the interviews and documents, you build the entity catalog. You organize it by domain:

Customer Domain (8 entities)

CUSTOMER, INDIVIDUAL_CUSTOMER, BUSINESS_CUSTOMER, CUSTOMER_ADDRESS, CUSTOMER_PHONE, CUSTOMER_EMAIL, CUSTOMER_DOCUMENT, DOCUMENT_TYPE

Key design decision: CUSTOMER is a supertype with INDIVIDUAL_CUSTOMER and BUSINESS_CUSTOMER subtypes. The discriminator is CUSTOMER_TYPE. Implementation: table-per-type, because individuals and businesses have fundamentally different attributes (DATE_OF_BIRTH vs. INCORPORATION_DATE).

Account Domain (6 entities)

ACCOUNT, ACCOUNT_HOLDER, ACCOUNT_TYPE, CHECKING_DETAIL, SAVINGS_DETAIL, CD_DETAIL

Key design decision: ACCOUNT_HOLDER resolves the M:N relationship. Even though most accounts have one holder, the joint account requirement makes this necessary. The cost is one additional JOIN in account queries — a modest price for correct modeling.

Transaction Domain (3 entities)

TRANSACTION, TRANSACTION_TYPE, PENDING_TRANSACTION

Key design decision: Separate tables for posted and pending transactions. Posted transactions are immutable (regulatory requirement). Pending transactions are mutable (status changes, cancellations).

Loan Domain (3 entities)

LOAN, LOAN_PAYMENT, LOAN_COLLATERAL

Key design decision: LOAN extends ACCOUNT through a shared primary key. Every loan is an account, so LOAN.ACCOUNT_ID references ACCOUNT.ACCOUNT_ID. This allows the unified account view the COO requested while preserving loan-specific attributes.

Product and Fee Domain (4 entities)

PRODUCT, FEE_SCHEDULE, INTEREST_RATE_SCHEDULE, BRANCH_PRODUCT

Key design decision: FEE_SCHEDULE and INTEREST_RATE_SCHEDULE both use effective dating with half-open intervals. This satisfies the product manager's requirement to track historical rates and the compliance officer's need for dispute resolution.

Branch and Employee Domain (2 entities)

BRANCH, EMPLOYEE

Key design decision: EMPLOYEE has a self-referencing MANAGER_ID for the organizational hierarchy. BRANCH has a MANAGER_EMP_ID for the branch manager (a 1:1 optional relationship with EMPLOYEE).

Regulatory and Audit Domain (4 entities)

AUDIT_LOG, REGULATORY_REPORT, CTR_FILING, ALERT

Key design decision: AUDIT_LOG uses the centralized pattern (VARCHAR old/new values) for column-level change tracking. Shadow tables will be added for CUSTOMER and ACCOUNT in the physical design phase for row-level historical reconstruction.

Online Banking Domain (2 entities)

ONLINE_PROFILE, LOGIN_HISTORY

Key design decision: ONLINE_PROFILE is 1:1 with CUSTOMER (optional participation on the CUSTOMER side). Only ~40% of customers currently use online banking.

Total: 32 entities.

Week 4: Normalization Validation

You translate each entity into a preliminary table definition and apply normalization checks.

Issue Found: TRANSACTION initially included CUSTOMER_NAME and BRANCH_NAME

Violation: 3NF — transitive dependencies (CUSTOMER_NAME depends on CUSTOMER_ID through ACCOUNT, not on TRANSACTION_ID).

Resolution: Removed. These are retrieved via JOINs. If statement generation performance requires denormalization, that will be a documented physical design decision, not a logical model feature.

Issue Found: LOAN initially included all collateral attributes inline

Violation: 3NF — COLLATERAL_TYPE, COLLATERAL_VALUE, etc. depend on the collateral, not the loan. Also, the loan officer mentioned that one collateral item can secure multiple loans.

Resolution: Created LOAN_COLLATERAL as a separate entity. Initially modeled as 1:N (one loan can have multiple collateral items). If the business confirms that one collateral item can secure multiple loans, this becomes M:N with a junction table.

Issue Found: CUSTOMER_ADDRESS initially used a single row with HOME_ADDR_LINE1, WORK_ADDR_LINE1, MAIL_ADDR_LINE1

Violation: 1NF — repeating groups (three addresses embedded as separate column sets).

Resolution: Created CUSTOMER_ADDRESS as a separate entity with ADDRESS_TYPE discrimination.

Week 5: Design Review

You present the model to all eight stakeholders in a two-hour design review session. The presentation includes:

  1. The entity-relationship diagram — a wall-sized printout with all 32 entities and their relationships in Crow's foot notation.
  2. The data dictionary — a spreadsheet with every entity, every attribute, data types, domains, and business rules.
  3. Scenario walkthroughs — you trace five common business processes through the model: - Opening a new joint checking account - Processing a mortgage application - Generating a monthly statement - Filing a Currency Transaction Report - Producing the FDIC Call Report

Each walkthrough demonstrates that the model supports the business process without workarounds.

Review Findings

Finding 1 (Compliance Officer): "We also need to track which employees access customer data. This is a SOX requirement." Resolution: Added DATA_ACCESS_LOG entity to the audit domain (not originally in scope, but critical).

Finding 2 (Branch Manager): "Can the model handle a customer who is an individual AND an authorized representative of a business customer?" Resolution: Yes — a single CUSTOMER record can appear in ACCOUNT_HOLDER for both personal accounts (as PRIMARY holder) and business accounts (as AUTHORIZED_SIGNER). The CUSTOMER supertype/subtype handles the attribute differences, while ACCOUNT_HOLDER handles the role-based relationship flexibility.

Finding 3 (CFO): "The PRODUCT entity needs a regulatory classification code — each product maps to a specific Call Report line item." Resolution: Added REGULATORY_CLASS_CODE to PRODUCT entity.

Week 6: Final Model and Handoff

The final logical model contains 34 entities (two added during the design review), 200+ attributes, and 45 relationships. It is documented in:

  1. A Crow's foot ER diagram (created in erwin Data Modeler)
  2. A complete data dictionary (one row per attribute with data type, domain, nullability, and business rule)
  3. A design decisions log (15 documented decisions with rationale)
  4. A glossary of business terms mapped to entity/attribute names

This model serves as the input to Chapter 14 (Physical Database Design), where every entity becomes a DB2 table, every relationship becomes a foreign key, and every design pattern becomes DDL.

Lessons Learned

  1. The stakeholder interviews revealed requirements that no single person knew completely. The compliance officer knew about CTR filings but not about joint account complexities. The branch manager knew about joint accounts but not about regulatory reporting. Only by talking to everyone did the complete picture emerge.

  2. The existing IMS schema was both helpful and misleading. It confirmed the core entities (CUSTOMER, ACCOUNT, TRANSACTION) but its embedded addresses and lack of audit trail would have been carried forward if we had simply replicated the old design.

  3. The design review caught two requirements that the interviews missed. This is normal. Design reviews are not optional — they are a required step in any serious modeling effort.

  4. Normalization validation found three genuine issues. All three were cases where convenience attributes had been included "because the old system had them." Normalization forced the question: "Does this attribute depend on this entity's key, or on something else?"

  5. The junction table (ACCOUNT_HOLDER) was the most debated decision. Some stakeholders wanted the simplicity of a direct CUSTOMER_ID foreign key in ACCOUNT. The joint account requirement made the junction table necessary, but the debate was healthy — it forced everyone to understand the tradeoff between query simplicity and modeling correctness.

Discussion Questions

  1. If Meridian Bank later decides to support international customers with addresses in non-US formats (no state code, different postal code formats), what changes would be needed in the CUSTOMER_ADDRESS entity?

  2. The ONLINE_PROFILE entity stores PASSWORD_HASH. Should this be in the same database as the core banking data, or in a separate database? What are the security implications of each choice?

  3. The model uses surrogate keys (system-generated integers) for all entities. A developer argues that ACCOUNT_NUMBER (a natural key) should be the primary key of the ACCOUNT table because "everyone refers to accounts by their number." How would you respond?

  4. The LOAN_COLLATERAL entity currently has a 1:N relationship with LOAN (one loan can have multiple collateral items). The loan officer mentioned that one property can secure both a mortgage and a HELOC. How would you change the model to support this, and what are the implications?

  5. The CFO's requirement for Call Reports requires aggregation across all accounts. How does the ACCOUNT supertype design (with subtypes CHECKING_DETAIL, SAVINGS_DETAIL, CD_DETAIL) support this requirement compared to a table-per-concrete-class design where each account type is a standalone table?

Return to Chapter 13 | Continue to Case Study 2