Re: Was: Triggers, Stored Procedures, PHP

"Shane D" <shane(dot)dawalt(at)wright(dot)edu> Wrote:
<snip>
>
> Could someone explain to me the usefulness of views? I understand
> how they are created. I understand a single query can be created as a
> view returning all records in a single column of a single table, or
> maybe even multiple columns across many tables using a complex join.

Before I go into the usefulness of views, it is important to understand that
views behave sort of like "logical tables" which can be used in SELECT
queries (or in PostgreSQL, if you add the proper RULEs, you can also use
them in INSERT, UPDATE, or DELETE queries as well).

A view is defined by a select query (often but not always a join). This can
be useful for:
1) Aggregating tables with different permission levels into a single
logical table, hence giving the effect of per-column permissions.
2) Subdividing the table into several logical tables with different
permissions based on which view the row appears in.
3) Providing application-specific presentations of the data, thus
insulating them from the actual structure, or allowing a denormalized view
of a highly normalized database.
4) Data mining and reporting: Views can aggregate tables in ways that make
it easier to make sense of data. Views can be aggregated into other views,
allowing very abstract approaches to reporting.

>
> That sounds find if all you want to do is to populate your drop-down
> list box with selection choices or use the same search criteria each
> time. But if I want to access certain information for a particular
> customer that requires joins and the like, then a view would be great.
> But as far as I know, I am unable to place search parameters into a
> view. Is this false or am I totally missing the point of views?
>
Think of it this way: PostgreSQL can do the following thigns with your
data:
1) Store it
2) Retrieve it.
3) Ensure that the data is meaningful (via Referential Integrity
enforcement, etc.)
4) Present it in various ways (i.e. complex select statements, views, etc.)

Views represent a tool for changing the presentation of the data in the
database. Neither more nor less.

For that join you are mentioning, one would have to know how you were
looking at the information, etc. to know whether a simple join would be the
best way to go or whether a view would be better.

Best Wishes,
Chris Travers