Use views for flexibility, maintainability and security

Database views are excellent for hiding the implementation of an SQL query. As long as the view name and column names remain the same, the definition of the view can change without requiring you to recompile your application. You can use them in a similar way to an Interface in Java or C#.

They can be uses to filter out rows or columns from a query:

Create view Current_users_v as
(
  Select ID, User_name, Hashed_password, Salt, Screen_name, Locked, Enabled
  From Users
  Where valid_from_date <= CURRENT_DATE
  And (valid_to_date >= CURRENT_DATE)
  And Enabled = 'Y'
);

For security reasons you may need to restrict a query so that it only shows data from one sales region:

Create view Sales_uk_v as
(
  Select Customer, Amount
  From Sales
  Where region = 'UK'
);

You may wish to provide a denormalised view of data to your front-end software e.g. a view of purchase orders that combines columns from the parent purchase order table and the child table containing the lines within a purchase order. This can be quicker and easier than querying the parent table followed by the child table.

Create view Purchase_orders_v as
(
  Select po.Order_ID, po.Supplier_id, pol.Order_Line_ID, pol.Item_desc, pol.Quantity, pol.Unit_price
  From Purchase_orders po, Purchse_order_lines pol
  Where po.Order_ID = pol.Order_ID
);

Some application servers come with a default authorisation facility to read roles from a simple database table with just columns for username and rolename. However your authorisation data my be nomalised into tables for users, roles and the intersection table users_roles, with start and end dates on roles. A view can form an adapter between the two autorisation models:

Create view Current_roles_v as
(
  Select username, rolename
  From Users u, Roles r, Users_Roles ur
  Where u.username = ur.username
  And r.rolename = ur.rolename
  And ur.valid_from_date <= CURRENT_DATE
  And ur.valid_to_date >= CURRENT_DATE
);