Hi Guys,
Hope you had a wonderful weekend !!!
In this blog I would share my personal thoughts on What is Data Base, Views, Tables and Reference Data?
Data Base is something like a House and Tables are like Rooms and Reference Tables are like Guest House
Now I bought a house I said to my mom and dad you guys please take the room in Upstairs, sisters please share the room near to the Kitchen, brothers please share the room near to the Balcony.
Like the example above an Organization will decide to keep their data into one Data Base (Home) but in different Tables (Rooms) and when they want to keep the data that changes as per Customer recommendation then they will capture in Reference Tables (Guest House).
Why we need View Data base ?
Again I will consider the home as example – when you are inviting your friends to your house you will not say how your house is physically build, but you will say your home address right? it is difficult to understand how they building is built rather then checking its address. Likewise the data will be loaded into Physical tables and developers will just write a Select Query on the tables , and that query will be used to create the View Database.
Primary Key and Foreign Key relationship :
Each room in your house are having a logical reference to the house and the rooms and the people are living in the rooms can be called or connected by this reference likewise in DB all the tables are Logically and Physically connected with a Column. So all the tables will be communicating each other using this Primary Key;
I will give you one more example :
I wanted to create an account with Citi Bank, I gave my Name, DOB, Address, Amount needs to be deposited, my mobile number, my telephone number, my email id, relevant documents.
Account Created
> After some days I gone to bank and applied for Credit Card !!! now they are asking my Account Number only !! Why ?
I got my Credit Card!!! Wohoooo
> After some days I got a call from bank and they offered me a Life Insurance – I accepted it , now what
I have Insurance from Citi Bank
> After some days I my wife was urging me to buy a House
I have bought a house with loan from my Bank
If you see the above scenario Bank needs to capture all the data related to my account !!! so what they gonna do ??
First step Create a Data Base called – ACCOUNTS
and now create tables !!! is only one table enough to accommodate all the data related to me ? Ghrrrr no !!!
They will create multiple tables for each types of Data !!
Table_1 – Primary Details of my account
Table_2 – Address Details of my account
Table_3 – Contact Channels (mobile , Email. Home Phone)
Table_4 – My account Balance Details
Table_5 – My Credit Card Details
Table_6 – My Home Loan Details
Now all theses 6 tables will have ACCOUNT NUMBER as my primary Key. If the bank person wants to see my Home Loan Balance and Credit Card balance he will join tables Table_5 and Table_6 using my Account number
I hope now you understood how the Primary Key functions
Reference Tables:
Using my account details I will explain you why Citi Bank wants to create the Reference Tables ?
When I applied Credit Card , they asked about my Annual Income, I said its AUD 100K, they punched the value in their system. After some days I got a letter from the Bank says that you got 85K as your Credit Limit. I wonder how they have captured this data when they extracted the credit limit for my application. Here is the culprit Reference tables comes into the picture
When my salary in => 100K then it will refer to the Credit Limit Reference table and fixes the credit limit of 85K. I would have said 50K then my Credit Limit would have been limited to 30K :-(.
Hope you guys understood the concepts of Data Base, Tables, Views and Reference Tables
See you guys in my Next Blog about Shake Down testing
Cheers – Asik