How do views reduce code duplication?
I read something similar about db views:
Views are incredibly powerful and useful for one reason that stands out above all else, for other very good reasons. They reduce code duplication. That is, in most cases, the bottom line. If the query will be used in three or more places, then the view will dramatically simplify your changes if the schema or query parameters change. I once had to modify 22 stored procedures to change some query logic. If the original architecture used views, then I would only have three changes.
Can someone explain to me how this works and maybe give some examples?
Respectfully!
a source to share
The view allows you to change the structure of the underlying table without affecting how your application sees the data. Because a view often represents a higher-level domain concept in one or more tables (for example, viewing Available Flights when built from the flights, fares, and airlines tables), they can represent complex ideas in a single way ...
Since the logic of how to turn raw database tables into views is captured in the database, the complexity of building them is less likely to reach your application. This means that if you use Available Flights
in many places and then something changes in the table Flights
, only the parts that explicitly depend on Flights
will need to change, not something about Available Flights
.
For this reason, it is considered a good idea to use views to reduce complexity and isolate the risk of breaking schema changes back into the database where it will not affect dependent applications.
a source to share
The view is similar to a predefined request. You are just writing a query SELECT
that returns the columns / data you want from the database tables - any query that returns a record can be used as the basis for a view. Then you create a view that provides the SQL query that you wrote as a view definition.
When you render SELECT
from a view, the database engine executes your query and returns the results as if you did SELECT column1, column2 FROM table
.
The best part about views is that you are not limited to one table. So, imagine you have three tables in a many-to-many relationship - user <-> user_to_role <-> role
.
You can write a query to get users and their associated roles:
SELECT u.user_name, r.role_name FROM user u INNER JOIN user_to_role ur ON ur.user_id = u.id INNER JOIN role r ON r.id = ur.role_id;
Now create a view using the above SQL definition (called user_role_view
) and you could do:
SELECT * FROM user_role_view
in your application and get a result set containing columns user_name
and role_name
, all linked correctly :-)
Used correctly, views can be very powerful and very helpful in reducing the complexity of your SQL queries at the application level.
a source to share
In simple terms, a view is a virtual table (i.e. you can query it as if it were a table) defined by some query. The reason they reduce code duplication can be illustrated as follows:
Suppose you have table T with columns C1 and C2. Then you have a couple of places in your application where you ask T as such: SELECT C1 FROM T WHERE C2 = 'cow'
. One day you realize that cow
this request is no longer required and you want to go to goat
. To make this change, you need to find each of the operators SELECT
and replace cow
with goat
.
If you were using binding V
to V
, your statements would look more like SELECT * FROM V
- then you could change V
instead of changing individual statements and thus make one change instead of multiple.
The MySQL link contains some good examples of how to use views.
a source to share
Note. The instructions below are just an example and won't actually work because of SELECT *. It is easier to show how the code can be shortened.
Take the following three similar queries.
SELECT
*
FROM
Table1
INNER JOIN
Table2 ON Table2.Id = Table1.Id
INNER JOIN
Table3 ON Table3.Id = Table2.Id
INNER JOIN
TableX ON TableX.Id = Table3.Id
SELECT
*
FROM
Table1
INNER JOIN
Table2 ON Table2.Id = Table1.Id
INNER JOIN
Table3 ON Table3.Id = Table2.Id
INNER JOIN
TableY ON TableY.Id = Table3.Id
SELECT
*
FROM
Table1
INNER JOIN
Table2 ON Table2.Id = Table1.Id
INNER JOIN
Table3 ON Table3.Id = Table2.Id
INNER JOIN
TableZ ON TableZ.Id = Table3.Id
Now if we were to create a VIEW like
CREATE VIEW View123 AS
SELECT
*
FROM
Table1
INNER JOIN
Table2 ON Table2.Id = Table1.Id
INNER JOIN
Table3 ON Table3.Id = Table2.Id
Now three queries can be written as
SELECT
*
FROM
View123
INNER JOIN
TableX ON TableX.Id = View123.Id
SELECT
*
FROM
View123
INNER JOIN
TableY ON TableY.Id = View123.Id
SELECT
*
FROM
View123
INNER JOIN
TableZ ON TableZ.Id = View123.Id
a source to share
http://msdn.microsoft.com/en-us/library/aa258253%28SQL.80%29.aspx
Here is a link that describes the syntax for creating views. One of the other advantages of views is that they are performed on the server, not the client. This means that they are much faster than a local query.
a source to share
A view is a macro. Nothing more, nothing less.
If you start browsing views in views, they can undo / expand server requests.
They have features like hide table schema changes and indexed / materialized views, but they're not a silver bullet. It is tempting to "encapsulate" your code, but it is wise to use
a source to share
Views reduce code duplication because most of the data-oriented logic can be encapsulated in the view during definition. The resulting managed data can be accessed by the application without the application, including the logic, or even be aware of it.
For example, imagine the view OrderDetailsEx:
CREATE VIEW OrderDetailsEx
(OrderID, OrderDate, ProductID, Description,
UnitPrice, Quantity, ExtendedPrice, Tax, TotalPrice) AS
SELECT O.OrderID, O.OrderDate, D.ProductID, P.Description,
D.UnitPrice, D.Quantity, (D.Quantity * D.UnitPrice),
C.TaxRate, (C.TaxRate * D.Quantity * D.UnitPrice)
FROM Orders O
INNER JOIN OrderDetails D ON O.OrderID = D.OrderID
INNER JOIN Products P ON P.ProductID = D.ProductID
INNER JOIN Customers C ON C.CustID = O.CustID
The application is now free to select records from this table whenever order information is required. There is no need to code the line expansion, tax calculation, and merge logic into the application.
If this information is only used in one place, it means that you just moved the logic from the application to the database. However, it is much more likely that the use of this information will be scattered and repeated throughout the application - in the trading module, when invoices are printed, when statements are printed, when accounts are posted, etc. If this is true, then you have eliminated all this duplicate logic and nested it in a single declarative statement in the database.
a source to share