Articles → SQL SERVER → Views for data abstraction

Views for data abstraction

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

Abstraction is a concept in which the details of implementation of object are hidden from the person who is using the object. A very common example that can explain the concept is 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.

Now let us move from real world example to SQL server and see how views are useful in attaining the abstraction in real world example.

Scenario – 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 following functionality from the system.

      1. Maintain customer information (i.e. name and address)

      2. Maintain customer payments record.

      3. Maintain customer orders.

      4. 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 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 and your client is using the system from a long time. Now after a span of 1 year the client revert back to you saying that now our business is expanded and we are looking to enhance the system. Now our requirement is that we want to display city and states as well in the monthly reports so that we can analyze data in more details.

So your approach would be

1. Add columns in the table

2. Change in all the stored procedure where you have used the table earlier.

The problem with this approach is this approach is tedious and monotonous and more error prone.

Now you have thought deeply about these problems and decided to take a more flexible approach so that if the changes like this will come in the future then you can accomplish that with minimum possible effort. So instead of 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. Now if in the future if 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 views 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