|
The table Diagram above is the SQLServer 2005 documentation tool for Viewing the Data Structures and Relationships in
a SQL Database.
In the above subsection of the database diagram, 3 related tables are shown. These tables are the Member, Adult, and
Juvenile tables and are the data storage structures used in the Database to store and retrieve Membership Information.
Is part of the Design Specification, the member is the base table for storing all primary member information. Its Primary
key is member_no and enforces a One to One Primary Key Constraint with the Adult and Juvenile tables. As I would
never recommend this structure as a DBA, as a developer we often have to work with structures we normally wouldn't design
ourselves.
The Specification for member is that a member can either be an Adult or a Juvenile. A Juvenile is any member under the
age of 18 and must have a sponsoring adult to be a member. With these business policies in place, any information
for address or expiration for any member is kept in the record of the sponsoring adult.
Instead of using literal TSQL code anywhere outside of the Database, the safest most reusable technique for processing
data manipulation language is in a Store Procedure or Stored PROC. Below is a section of the Stored Procedure Responsible
for Adding a new member.
In a normal Stored Procedure, Parameters are defined and passed in, local variables are declared for use within the Procedure,
and the data validations are performed before any inserts, updates, or deletes are performed.
Most business operation transactions require changes to multiple tables in the database in order for the information
system to accurately and correctly reflect the entire transaction.
Because database transactions often behave in an "all or nothing" type of requirement.
The use of the "begin tran" and "end tran" keywords are used to logically group a series of database manipulations
which, if all complete successfully, will then save and be applied to the database.
In the case of an error, the transaction can be "rollback" ed in the catch block for the error.
There are several advantages to using Stored Procedures but for the purposes of this example I will list a few.
1. More Secure - you are passing parameters not TSQL to the database and that creates another layer of protection
and helps to prevent SQL Injection.
2. Source is easily found - If twenty different processes were capable of adding a
member they could all be funneled through the stored procedure for consistent transactions.
3. Centralized logic - if the
rules changed, one central update is better than twenty changes throughout the application code.
4. No One but the local
development team can access the actual code- all outside developers ever see is the signature and parameter list. Makes things
a lot tougher for malicious programmers.
With all that being said.... the next section will take a look at the Data Access Layer which is written in C# and ADO.net
for a safe and reliable connection between the database and the n-tiered .Net Application.
|
