Step 3: Create database views to express multi-table joins and complex queries

A database view is simply a subset of the database sorted and displayed in a particular way.  A database view limits the records displayed and could contain a subset of records, such as "products that are in stock", or "customers that have purchased goods greater than $1000".  A view could also contain a subset of the information for each item, such as "product name, description and price", but not the dimensions of the product.  Similarly, the order of the columns is specified in the database view as well.  Some applications use database views or stored procedures to embed security in the database so that the application cannot compromise security regardless the query used.

Because database views are an integral part of your underlying database structure, it’s important that your application embrace them.  Many organizations use database views as a way of consolidating data for reporting and other uses where a common “definition” is required.

Applications built with Iron Speed Designer fully support database views.  For each view, you can control which columns are displayed, what order they are displayed in, how wide each column is, how the data is sorted, and what types of records to display.  In general, you can perform any operation on a database view in Iron Speed Designer that you can on any table with the exception of inputting data into the view.  This must be done by inputting data into the underlying tables from which the database view is derived.

You cannot create database views in Iron Speed Designer.  Views must be created with other database tools before Iron Speed Designer can use them.

Iron Speed Designer creates a full suite of web pages and code for database views, just as it would for a normal database table.  Iron Speed Designer treats them the same.  Database views are great for expressing multi-table joins and for complex queries containing AND, OR, NOT, nested queries, etc.  Database views are exceptionally good for expressing filtering criteria.


Database view


Current quarter sales

Filters results based on time

Paid invoices

Filters results based on status field

Iron Speed Designer is not a query construction tool and it has comparatively weak facilities for expressing multi-table joins and filtering criteria.  We strongly encourage you to use database views for all but the simplest relationships.

Make your database views updatable

Most database views are not updatable, meaning that new records cannot be inserted into them and their underlying tables.  Iron Speed Designer detects if a view is updatable and creates Add Record, Edit Record and Edit Table pages only for updatable views.

To make the database view updatable, you must declare a primary key for your database view in your database.  Unfortunately you cannot make a view updatable by creating a Virtual Primary Key for the view in Iron Speed Designer.  The database needs the primary key in order to update records.

See Also

Step 1: Create child tables for one-to-many relationships

Step 2: Create separate lookup tables

Step 3: Create database views to express multi-table joins and complex queries

Step 4: Explicitly declare primary keys in your database

Step 5: Explicitly declare foreign keys in your database

Step 6: Declare virtual primary keys in Iron Speed Designer

Step 7: Declare virtual foreign keys in Iron Speed Designer