Articles → SQL SERVER → Views For Data Abstraction

Views For Data Abstraction

In this article, we will explain about a very important use case of view i.e., abstraction.

What Is An Abstraction?

Abstraction is a concept where the details of implementation are hidden. A very common example that can explain the concept of abstraction is the car. A person who is using the car is not concerned about how the car is functioning internally. His only concern is using the car to go from one place to another.

Let us move from real-world examples to the SQL server and see how views are useful in attaining abstraction.

Scenario

Consider a scenario where you are responsible for designing a database for a system. The system is for a small-scale bakery shop. The system is made as per the requirements given by the client. The client wants the following functionality from the system: -

Maintain customer information (i.e., name and address)
Maintain customer payments record
Maintain customer orders
Monthly Reports

So, with these scenarios in mind, you have created 3 tables.

tbl_customer_information

customer_id	customer_name	customer_address

tbl_customer_orders

order_id	customer_id	order_details

tbl_order_payments

payment_id	order_id	payment_details

Note → There can be many more tables to fulfill the requirement.

Now, using the table schema you create a stored procedure to get the information about the customer and display it on the customer list page.

create procedure sp_get_customer_information As begin 
select 
  customer_id, 
  customer_name, 
  customer_address 
from 
  tbl_customer_information end

For the sake of monthly reports, you have created another stored procedure.

create procedure sp_get_monthly_reports As begin 
select 
  ci.customer_id, 
  ci.customer_name, 
  ci.customer_address, 
  co.order_details 
from 
  tbl_customer_information ci 
  inner join tbl_customer_orders co on ci.customer_id = co.customer_id end

Note → There are many more stored procedures you will create, but here I am listing just 2.

The system is delivered to the client. Everything is working fine with the system; your client has been using the system for a long time. After 1 year, the client has reverted to you saying that our business has expanded and we are looking to enhance the system.

The requirement is that we want to display cities and states as well in the monthly reports so that we can analyze data in more detail.

So, your approach would be: -

Add columns to the table
Change all the stored procedures where you have used the table earlier

The problem with this approach is that approach is tedious monotonous and more error-prone. Now you have thought deeply about these problems and decided to take a more flexible approach so that if changes like this will come, you can accomplish that with minimum possible effort. So instead of a table, you have used views.

create view v_customer_information as 
select 
  customer_first_name, 
  customer_last_name, 
  customer_city, 
  customer_state 
from 
  tbl_customer_information

Now the stored procedures are changed to

create procedure sp_get_monthly_reports As begin 
select 
  ci.*, 
  co.order_details 
from 
  v_customer_information ci 
  inner join tbl_customer_orders co on ci.customer_id = co.customer_id end

Similarly, you will update all the stored procedures with the views. If in the future the client wants to add country in the reports as well then you can just do the change in one place i.e., views. So, here you are hiding details about the view internals and still allowing the stored procedures to use the view.

Posted By -	Karan Gupta

Posted On -	Wednesday, March 30, 2011

Query/Feedback

Your Email Id

Subject

Query/Feedback	Characters remaining 250