Database Reference
In-Depth Information
straction layer. In the example of splitting one table (TableA) into two ta-
bles (TableA and TableB), any code that selects, inserts, updates, or deletes
from TableA must be changed to perform its action on both tables. If we
have an abstraction layer, we need only modify a few stored procedures
and views to accommodate the new structure.
You are probably thinking, “Code will need to be modified either way,
so what's the difference?” Although it is true that in either case you need
to make a change, the difference is huge. For starters, unlike code in a
.NET application, code on SQL Server is not compiled; therefore, if the
procedure takes the same inputs and returns the same outputs, you can
alter that procedure in the middle of the day while users are online and no
one will know the difference. Granted, there are other considerations
based on what else you have to change in the database, but we are talking
about the transparency of the stored procedure change. The second, and
probably the more important, difference in modifying stored procedure
code over application code is that there is only one database. If you change
application code, you often need to recompile and then have all your users
update or reinstall the client application.
Abstraction layers give you the flexibility to make changes for per-
formance reasons, for normalization, and for reasons of extensibility.
Future growth and changes are always similar with an abstraction layer.
to the Logical Model
When it's time to build your abstraction layer, you should find that it more
closely ties to the logical model than to the physical model. You should
work back toward your entities if you want things to make the most sense
and provide the most benefit. This practice allows applications and users
to work with your database in terms that make sense to them. Remember
that your logical model is more user friendly than your physical model,
which is set up to store data in a SQL Server friendly manner. This means
that you should avoid the convention of creating four stored procedures for
each table: one for insert, one for update, one for select, and one for
delete. You may laugh, but we have seen this done. If you go this route, all
you have done is to add an extra layer to a DML statement. Instead, you
should look to create procedures for saving a customer entity, procedures
Search WWH ::
Custom Search