Database Reference
In-Depth Information
contain a NULL value. We select data only from tbl_address in this view.
However, in some instances you may find yourself needing to display
more customer data in addition to the address data—for example, the
customer_id column. If this is the case, you need to join the two tables.
In this instance we have no need for additional information, so we
forgo the join and save a little on resources. Because we have these two
views, the application developers can decide which one they need to ref-
erence in different places in their application and retrieve only the data
they absolutely need.
Stored Procedures
The same rule goes for stored procedures as for views. When you create
stored procedures, think in terms of the entities they are meant to affect.
Doing so will help you avoid the pitfalls we discussed earlier around writ-
ing stored procedures to simply insert, update, or delete data. Consider
creating standards that govern how and why stored procedures are created,
what entities are being affected, and what the potential data manipula-
tions are.
How do you correctly implement stored procedures in your abstrac-
tion layer? There are a great many correct answers to this question, be-
cause it depends on the environment and on your preferences. We look at
some of your options here, but remember that this is not the absolute cor-
rect answer in every situation.
A commonly used option is to create a single stored procedure for each
entity; the procedure will update or insert data in all the associated tables.
This is usually the least confusing option, but it can make for some tricky
code. First, let's walk through the logical steps that a stored procedure
must perform to save changes to a Customer entity in the Mountain View
Music database, and then we will look at some code. When the stored pro-
cedure runs, it must first determine whether or not a record already exists.
Luckily, you followed good modeling guidelines, so each and every table
has a primary key defined, and that makes this step easy. If the record ex-
ists it should be updated; otherwise, it should be inserted. In addition, the
stored procedure should return any information about what it did and
which record was updated.
Sound simple? It can be if you do your job correctly. The following is
an example of a stored procedure that performs the process we just dis-
Search WWH ::
Custom Search