Views are Your Friend

One of the challenges I see regularly in my work as a database administrator is finding a way to bridge the gap between the well-normalized physical implementation of a database behind an application and providing a way to make the data available in an easy-to-use (and understand) fashion for the end user.  For many clients this means letting them design their own reports.  Database views are my go-to method for making a complex database structure easy to use for end-users and even developers.

Views have a number of advantages.  For one, they are available in all SQL databases, including SQLite.  Views allow you to reduce the complexity of the end user experience by managing and limiting the data presented.  This might mean taking a very complex query joining multiple tables and presenting it in a single table with the end user (or developer) not needing to know the gory details of the SQL behind it.

This last point is important for the developers too.  Having the programmers figure out the complex joins for a query pulling from multiple tables is sometimes challenging and not always a good use of their time.  Having the Xojo application do all that work for a complex query is sometimes painfully slow.  Views not only speed up the development process but make the Xojo application more efficient since the database has already created the view and optimized it internally.

Views are more secure since you can limit the data shown and control who has the rights to view it.  By giving users and developers access to the underlying tables you may be exposing sensitive data.  Views are an easy way of sanitizing data.  We use views as read-only constructs so the user cannot update the data (note:  not all databases treat views as read-only).

Views can be created, modified and deleted via simple SQL statements.  The syntax of the statements (in particular for modifying views) varies a bit from database to database but the details can be easily found.  To create a view, the syntax is:

    CREATE VIEW viewname AS

SELECT (fill in your query here)

We recently used views implementing a large customer records management (CRM) system for an insurance broker.  Their database has around 100 tables containing information on customers, policies, related products, agent and commission data.  The data is normalized and it isn’t always straightforward to get related data.  The client does not have a dedicated IT staff and reporting is the primary responsibility of a part-time administrative employee, “Jane”.

Our goal was to provide Jane with the easiest means possible to write any report that was requested by management.  While Jane has some knowledge of databases and foreign keys, she isn’t technical and doesn’t have any training on the SQL language.

Our answer was to use our desktop reporting tool, BKeeney Shorts, customized to only show her views created solely for reporting.  (We did this via a simple naming standard in the database.)  She is presented with options such as:  Agent Commission, Prospect Contact and Client Policy Details.  If she needs an additional view, it’s easy to add it to the database and not impact the Xojo code behind the application. (It doesn’t require a recompile and redeployment of the software).

The view incorporates the calculations behind the data, so the reports are always consistent in terms of things such as how commission checks are calculated or who is the primary agent for an account.  By using views we ensure that consistent results are shown to the end user even if someone else is assigned to write reports

Views provide a level of abstraction between the database and the end user and we find  them very useful.  For the developers, it means they don’t have to figure out the complex queries and have the Xojo application create inefficient and complex queries.  Views are your friend!

5 thoughts on “Views are Your Friend

  1. Creating a temporary database that contains all the required data has our preference.
    This way the production data can’t be screwed up by the user and give him only the data he is entitled to see.
    We also replace/add fields with data from another table with the actual value.
    e.g. userid is replaced or added as an extra field with the username
    We create a temporary database just before opening the Report Designer and remove the temporary database when the Report Designer is closed.
    Just our 2 cents.

    • Views let you do much the same thing since we don’t allow the user to update them. In our views we have UserID as well as UserName with the link from the appropriate table.

      The only problem I could see with using the temporary database is that depending upon the amount of data it could be really slow creating it. We have a db server with several million rows so copying all of that out even into a temporary database is just too slow.

  2. Be aware that views can be horrible on your performance. (Unless its database that supports parameterized views) a lot of bigger applications and cloud apps have stopped using them or limited severely use of views.

    Yes many databases precompile views and gain slight performance enhancement from that but you loose it all and a lot more in actual uses cases.

    Like in the past many would make a view that contains Select a,b,c from Customers where Deleted = 0

    And then they do queries on top of the view. But issue is it was not fastest or most logical to query first on the deleted column, chances are they were filtering on Address or something else which would have resulted in far faster query to filter first on Address then Deleted. And performance gets worse the more complex queries you involve the view with, like joins on the view for example. And you can easily measure this all for example with the MS SQL Server.

    • Some DB’s handle views much better than others. I’ve worked with some huge DB’s where views were used extensively and had no performance issues. We spent a lot of time tuning our views and overall DB so this was the case.

      As well, if you have slow views you might want to look into materialized views – which is NOT created using “CREATE VIEW”
      PostgreSQL and Oracle support them.
      MS SQL calles them “indexed views” – but they are much more restrictive in what they support and not quite the same as materialized views.

Comments are closed.