Small linux deployments

Views are a very powerful SQL feature and they’re used very seldomly. Basically they allow you to take a SELECT statement and make it appear as a virtual table within the database, that can be SELECTed, etc. This allows you to neatly prevent database schema changes from breaking your applications.

Suppose you have three separate applications that all read from the same database. One application may require an additional field, but how can you be sure that other applications don’t rely on the order or number of fields returned?

To combat this wrap the data tables in views like:

CREATE VIEW v_customers_schema31 SELECT * from customers;

This snapshots the current columns in the customers table as v_customers_schema31 so that any column additions won’t effect the results of SELECT * from v_customers_schema31.

This can be especially useful if you generate many different text file output products and don’t want to inadvertently change the format of a specific data consumer’s machine-readable file. For more information on the MySQL implementation of views, have a look at http://dev.mysql.com/doc/refman/5.0/en/create-view.html