Data Warehousing, BI and Data Science

28 April 2012

Bank Data Model

Filed under: Business Knowledge — Vincent Rainardi @ 2:33 pm

I created a retail bank data model in April 2012 for my nephew, who is studying Information System, in order to explain to him what a database of a bank might look like. Rather than concentrating to the data type (length of each column, numeric 20 or 16, etc — and the column lengths are different from bank to bank), I decided to concentrate on the data values, which to him is much more valuable, in order for to give him a good view and experience about the banking industry. I made the data myself. Minimalist, only about 5-10 rows per table, but it gives him an idea of what the data could be.

I put the database is in Excel, so that he doesn’t need SQL Server or Oracle to view it.
The link is here: bank-data-model2

It is a normalised model. The aspiration is to do it to 3NF. But sometimes for practical reason I didn’t do it to 3NF (e.g. only 2NF). This is the list of tables:

  1. Customer
  2. Address
  3. Telephone Number
  4. Customer Address
  5. Customer Telephone
  6. Account
  7. Account Holder
  8. Occupation
  9. Transaction
  10. List Lookup
  11. Product
  12. Branch
  13. Employee
  14. Currency
  15. Interest Rate Scheme
  16. Interest Rate
  17. Product Interest Rate
  18. Transaction

For illustration, below are the screenshots of 4 tables: Customer, Account, Interest Rate Scheme, and Interest Rate

Customer Table1

Customer Table2

Account Table

Interest Rate Scheme Table

Interest Rate Table

Transaction Table

Advertisements

12 Comments »

  1. Account can further be normalized to capture Status/ Types of Balances

    Comment by Praveen — 22 June 2013 @ 8:05 pm | Reply

  2. What is the difference between account holder and customer

    Comment by Joseph Khalil — 11 September 2015 @ 7:25 am | Reply

    • Account Holder table contains information about which customer(s) own which account(s). It is many to many relationship.
      I agree that Account Holder is not a good table name. Account Ownership is probably a better table name. Or Customer-Account Relationship table.

      Comment by Vincent Rainardi — 11 September 2015 @ 6:07 pm | Reply

  3. Good day, the link seems to have expired. Would you be able to re-upload it by chance?

    Comment by Dan — 22 September 2016 @ 8:17 pm | Reply

  4. what is list lookup

    Comment by Jigna — 26 September 2017 @ 2:07 am | Reply

    • Hi Jigna, list lookup is when you have a list, and you want to find something in that list. For example, your list is A, B, C, D, E, and you want to find C.

      Comment by Vincent Rainardi — 26 September 2017 @ 7:35 am | Reply

  5. “Account can further be normalized to capture Status/ Types of Balances” – what is the “correct” way to do that: mini – dimension, snow-flake, a fact table?

    Comment by dafari — 18 February 2018 @ 11:29 pm | Reply

    • Hi Dafari, we are not talking about Dimensional Model here. The Banking data model that I put there is a normalised model. So the correct way to normalise the Account Status (On-boarding, Active, Blocked, Closed) and Account Type (Saving Account, Current Account, Mortgage Account) from the Account table above is by creating 2 tables: Account Status table and Account Type table, with a Primary Key which is an identity(1,1) column. We then put these 2 Primary Keys in the Account table as account_status_id and account_type_id.

      Comment by Vincent Rainardi — 19 February 2018 @ 8:34 am | Reply

  6. Can you please come up with an article that talks about the investment bank’s Data model especially related to research data.

    Comment by veera — 30 July 2018 @ 3:42 pm | Reply

    • Hi Veera, I don’t have the data model, but the database for research basically consists of these 5 areas:
      1. Publication data: published reports along with their content and data, clients, subscribers
      2. Company data: market cap, share prices, sales, debts, dividend, cash flows, ratios, ESG, non financial data, industry sector aggregates
      3. Market data: interest rates, currencies, commodities, economics, government policies
      4. Event data: interest rate decisions, GDP, CPI, NFP, industrial production, foreign reserves, exports, imports, corporate earnings, dividends, splits, IPOs, credit events
      5. News data: stock markets, currencies, companies news, business news, politics, directors dealings, corporate actions, sectoral news, M&As
      All of these are time series (date-based) data. If you need more information about any of the areas above please let me know.

      Comment by Vincent Rainardi — 30 July 2018 @ 10:12 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: