How do views reduce code duplication?

I read something like this about db views:

Views are incredibly powerful and useful for one reason that stands out above all the other very good reasons. They reduce code duplication. That is, in most cases, the bottom line. If a q开发者_Go百科uery will be used in three or more places, then a view will drastically simplify your changes if the schema or query parameters change. I once had to edit 22 stored procedures to change some query logic. If the original architecture had utilized views, then I would have had only three changes.

Can anyone explain to me how it works, and maybe give me some examples?

Best regards!

---

A view allows the underlying table structure to change without affecting how your application sees the data. Because a view often represents a higher-level domain concept across one or more tables (e.g. an "Available Flights" view when constructed from "flights", "fares", and "airlines" tables), they can present complex ideas in a unified way.

Because the logic of how to turn the raw database tables into views is captured in the database, the complexity of their construction is less likely to reach your application. That means that if you use Available Flights in many places, and then something changes about the Flights table, only the pieces that explicitly depend on Flights will need to change, not anything about Available Flights.

For this reason, it's considered a good idea to use views to reduce complexity and isolate the risk of breaking schema changes back into the database, where it won't affect dependent applications as much.

---

Note: The statements below are only an example and would not actually work due to the SELECT *. It is more just to show the possible reduction in code.

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 such as

CREATE VIEW View123 AS
SELECT
    *
FROM
    Table1
INNER JOIN
    Table2 ON Table2.Id = Table1.Id
INNER JOIN
    Table3 ON Table3.Id = Table2.Id

The three queries could now 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 view is like a pre-determined query. You simply write a SELECT query that returns the columns/data you want from your database tables - any record-returning query can be used as the basis of a view. You then create the view giving the SQL query you wrote as the view's definition.

When you do a SELECT from the view, the database engine executes your query and returns the results, as if you'd done SELECT column1, column2 FROM table.

The best part about views is that you're not restricted to a single table. So imagine you have three tables in a many-to-many relationship - user <-> user_to_role <-> role.

You could write a query to get the 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 then execute:

SELECT * FROM user_role_view in your application and get a result-set containing the user_name and role_name columns, all linked correctly :-)

When used correctly, views can be very powerful and very instrumental in reducing the complexity of your SQL queries at the application layer.

---

Simply put, a view is a virtual table (that is, you can query it as if it were a table) defined by some query. The reason they reduce code duplication can be illustrated as such:

Suppose you have a table T with columns C1 and C2. Then, you have several places in your application where you query T as such: SELECT C1 FROM T WHERE C2 = 'cow'. One day, you realize that cow is not the value you need for this query anymore, and you want to change to goat. To perform this change, you would need to locate each of the SELECT statements and replace cow with goat.

Had you been using a View V bound to SELECT C1 FROM T WHERE C2 = 'cow', your statements would look more like SELECT * FROM V - then you could change V rather than changing the individual statements, and thus make one change instead of several.

The MySQL reference provides some good examples on how to use views.

---

http://msdn.microsoft.com/en-us/library/aa258253%28SQL.80%29.aspx

Here is the link that describes the syntax for creating views. One of the other benefits of views is that they are executed on the server instead of on the client. Meaning they run much faster than a local query would.

---

A view is a macro. Nothing more, nothing less.

If you start to JOIN views on views, then these can unnest/expand into server killing queries.

They have uses, such as hiding table schema changes and in indexed/materialised views, but they are not a silver bullet. It's seductive to "encapsulate" your code, but use judiciously

Is writing a query using Views a good strategy?

Tony Rogerson: VIEWS - they offer no optimisation benefits; they are simply inline macros - use sparingly

---

Views reduce code duplication because a great deal of data-oriented logic can be encapsulated in the view at the time of definition. The resulting manipulated data can be accessed by the application without the application including the logic or even being aware of it.

For example, imagine a 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

Now, the application is free to select records from this table whenever order information is needed. There's no need to encode the line extension, tax calculation, and join logic into the application.

If this information is used only one place that means you've simply relocated the logic from the application to the database. However, it's much, much more likely that use of this information is going to be scattered and repeated throughout the application — in shopping module, when invoices are printed, when statements are printed, when accounts are reviewed, and so on. If that's true, then you've eliminated all that duplicated logic and stuck it into a single declarative statement in the database.