Database Reference
In-Depth Information
Views
When you create views, your goal is to display the entity to the end user in
a way that makes sense based on the user's requirements. This might mean
joining several tables to provide your output, or it might mean querying a
single table but not displaying all the rows. A view that returns all the in-
formation about a customer is great, but if you also join to the address
table, you could receive multiple addresses for that customer and hence
receive multiple rows in your view per customer. This isn't necessarily a
problem, but the consumers of the view need to understand what they are
receiving.
Also, it's a good idea to create views that shortcut application logic. In
other words, if the application often pulls a list of all the past orders of a
customer to output an account summary page, then a view that joins the
customer table to the order table might save time and prevent the appli-
cation from making two trips to the database and then combining the
record sets locally.
Also, avoid using
SELECT *
syntax or
INSERT
syntax without a list of
values. In fact,
avoid
may be the wrong term; just don't use this syntax.
Doing so creates problems and reduces the usefulness of your abstraction
layer if changes are made. Either form of this syntax relies on the data
being in a particular order. When the application performs a
SELECT *
, it
might be expecting 15 columns of specific data. If you now add a new col-
umn before column 5, the application will still get 16 columns, but begin-
ning at column 5 the data will be different from last time.
This kind of unexpected result can trigger a simple problem such as
data being labeled incorrectly, or it can create bigger problems, such as ap-
plication crashes due to incorrect data types, divide by zero errors, item
not found in collection errors, and the list goes on. If you select data by col-
umn name, the worst the application will do is ignore the new column until
you modify the view definition.
Let's look at the views we will need to view our customer data.
Remember that the customer entity is made up of both tbl_customer and
tbl_address, so we must decide how to return this data. In this case, we will
use two views: one for all the customer information except for address
data, and the other for address detail. Had we decided to use only one
view, we would end up repeating all the customer data for each address in
the database. Although this isn't really a problem, it is often easier to re-
duce the duplication in the view rather than rely on the application to take
care of it. For view 1, we use the code shown next.
Search WWH ::
Custom Search