Why is there such pervasive abuse and misuse of database views today? Views are a helpful tool in building a business intelligence environment, yet many organizations establish practices that not only rob views of their full usefulness but present patterns that actually confuse issues instead.
It is not unusual to find a chaotic web of views on top of views pointing back and forth across multiple database schemas. This spaghetti-esque trail of views often results from a lack of control and policy guiding the team in the usage of views.
Nominally, views are named SQL queries. These named-queries can be used within other queries as if they were physical tables. Therefore, inside a given SQL query, users may incorporate tables or views without any concern over whether a used relation is implemented within the database as a table or as a view.
There is at least one current vendor that recommends using views for all user access to overcome a potentially painful shortcoming in their internal data locking process. Some pundits recommend using views across all the physical tables in order to establish a semantic layer over the data, the idea being that such a semantic layer can provide a level of independence between data users and backend data changes.
Should some table physically change, then when needed, a view could provide a stable and unchanged presentation to the users. However, this independence is sometimes threatened by departmental rules requiring tables and views to follow different naming conventions, forcing the view to have a different name from the table.
Even if an organization grants a waiver for following the rule for this specific purpose, it leaves a confusing trail of named objects. If an organization requires all user access to be through views, then this shortcoming can be somewhat avoided. Unless, of course, they have further departmental rules about naming views differently when based on a single table versus multiple tables underneath those views.
Having a consistent semantic layer isolating users from the base tables is a helpful practice. When significant change occurs across the base tables, an organization does have a chance to either temporarily or permanently hide that change from users, which assists in managing change and balancing the use of the often limited development resources.
Views can be useful when different areas within an organization have variant business terms for items. One set of views can be established for one group’s use, while a second view set can use the same base tables and invoke the alternate group’s linguistics. Views also assist by hiding complexity.
Situations sometimes arise wherein several tables need always to be joined, or always joined with some lengthy filtering clauses to fit a specific circumstance. Writing such logic into the SQL behind a view allows for users to see only the result of that complex logic as if it were an existing table.
In establishing views, it is important to avoid establishing naming conventions where tables are named differently for views, and single table views are named differently from multi-table views, as such practices are focused on allowing the IT staff to know immediately if they are dealing with a view or not.
That information is available for them to look up, if they truly need to know. It is best to name tables and views in terms of the business semantics that they are instantiating, so as to be business focused. Also, it helps to set up a standard whereby people are not simply pointing across all of creation to make views happen. Establish a rhyme and a reason within your organization that is sensible and as simple as possible. On top of everything, make sure the views created perform well. There are many tools that can assist in that, starting with making sure one evaluates an EXPLAIN plan as one develops each view.