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 );