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!