Hello Grant,

> This may seem odd but what is a "View"

A "view" is exactly what it says it is, it's a "view on data". They are
constructed by an SQL statement, eg:

create view employees_with_huge_salaries
as
select * from employees
where salary > 200000

This will "view" only the data for employees with salaries I should
be getting instead of them.

Views can be used like base tables.

For example, think of this query which returns departments where
there's at least 1 employee with a "huge salary":

select d.*
from departments d
where exists(select * from employees e where
e.department = d.departmentid and e.salary > 200000)

Instead of constantly re-writing the salary part, we could also write:

select d.*
from departments d
where exists(select * from employees_with_huge_salaries e
where e.department = d.departmentid)

> Where would I use them and why ?

For example, to make complex SQL statements easier. In the above
example, only a single view was used with a simple WHERE clause,
but think of this:

Table CONTACTS
ContactID
Name
Adress
etc...

Table VENDOR_CONTACTS
VendorID (ref link to Contacts.ContactID)


Now, to get Vendor addresses, we could do:

select c.*
from contacts c join vendor_contacts v on (v.vendorid = c.contactid)

Or, create a view that says:

create view VENDORS_FULL
as
select c.*
from contacts c join vendor_contacts v on (v.vendorid = c.contactid)

Now, we can always use the VENDORS_FULL view in place of
the join.




Another usuage example.

Think of a database that has a table COMPANIES which includes a
single "contact_name" and "contact_email" fields.

Next, the requirement for that database changes, and it should be able to
store multiple contacts per company.

You can cut-up the table COMPANIES to drop the "contact_name"
and "contact_email" fields. Create a COMPANY_CONTACTS
table like this:

COMPANY_CONTACTS
CompanyID
Contact_Name
Contact_Email
DefaultContact

Now, obviously, all applications that use the "contact_name" field in
COMPANIES will break. Unless, you rename table "Companies"
to "COMPANIES_V2" and create a view COMPANIES that does
something like:

create view COMPANIES
as
select c.*, cc.contact_name, cc.contact_email
from companies_v2 c
join company_contacts cc on (c.companyid = cc.companyid)
where cc.defaultcontact = 'TRUE'

This would make the underlying change in base tables transparent for
existing applications that would only ever use a single contact and you
can adjust applications that need multiple contacts.

Pretty neat, ey.


All sorts of stuff can be done in views, also restricting, for example,
access to rows:

Table EMPLOYEES_TABLE

View EMPLOYEES
as select * from employees_table
where department <> 'MANAGEMENT'


This would give normal users only a "view" of employees that aren't
part of management. For example, so that their salaries cannot be seen ;-)


Get the idea?

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com