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:
- Customer
- Address
- Telephone Number
- Customer Address
- Customer Telephone
- Account
- Account Holder
- Occupation
- Transaction
- List Lookup
- Product
- Branch
- Employee
- Currency
- Interest Rate Scheme
- Interest Rate
- Product Interest Rate
- Transaction
For illustration, below are the screenshots of 4 tables: Customer, Account, Interest Rate Scheme, and Interest Rate
Account can further be normalized to capture Status/ Types of Balances
Comment by Praveen — 22 June 2013 @ 8:05 pm |
true
Comment by Vincent Rainardi — 22 June 2013 @ 10:16 pm |
What is the difference between account holder and customer
Comment by Joseph Khalil — 11 September 2015 @ 7:25 am |
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 |
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 |
Hi Dan it’s fixed now.
Comment by Vincent Rainardi — 23 September 2016 @ 10:56 am |
what is list lookup
Comment by Jigna — 26 September 2017 @ 2:07 am |
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 |
“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 |
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 |