Database Benefits


Basing an application on a database, rather than just a loose collection of files, can have significant business benefits.  A database can be used to apply data validation techniques that help guarantee the integrity of the data.  This is important because a database with bad data can be more than inconvenient, it can cost your business time and money.

 

The data in a database is only good if it is input in a controlled manner, with as much validation as possible applied at the moment the data enters the database.  Business applications can be written to supply validation, but it is much better if the database itself provides the discipline.  This way, any time the data is used throughout the application, the validation is applied.  Following are seven techniques available in modern relational databases, such as Microsoft Access and SQL/Server, that can be used to maintain the integrity of the data.


Formatting:

A format can be applied to fields using an input mask to make sure the data follows a predefined layout.  For example, date fields can be formatted 'mm/dd/yyyy' to assure that a 2-digit month, a 2-digit day and a 4-digit year are input; the month is in the range 1-12 and the day is in the proper range for the month and year.  The input mask will automatically place the slashes in their proper position.


Validating:

Fields can be validated to fall within a specified numeric range, or to have specific text values.  For example, a field that is to hold the salutation prefix of a persons name can be limited to the values "Mr.", "Mrs.", "Ms.", and "Miss".


Requiring Fields:

A field can be deemed required, which means that it must be entered whenever the record containing the field is added to the database.  This guarantees that data vital to the application is entered when needed.


Indexing:

A field in a database table can be indexed. This technique is used more to improve the performance of the database rather than to improve the integrity of the data.  Indexed fields can be used to speed up database searches, but when an indexed field is designated as unique then it also means that the value entered in the field can occur in the database only once.  This concept can be extremely important in guarantee database integrity.


Looking up values in Tables:

This technique is used to guarantee that one of a predetermined set of values is entered into a field. For example, the following Category Code table is used to make sure that the value entered into a customer record is one of six predefined values from the 'Code' column shown below.  By maintaining the codes in a table, the user can control what codes are used throughout the application.

Category Code Table 

Access and SQL Server Database Design


Applying Referential Integrity:

"Referential Integrity" is a database term that has significant business ramifications.  Not understanding this concept is the single most critical mistake I find in 'home grown' database applications.

 

Applying referential integrity wherever possible is critical to keeping your data accurate.  Without it, garbage data can creep into the database and your entire application could become virtually useless.  The following example using the Microsoft Northwind sample database illustrates how this is done.

 

Northwind Relationship Diagram

 

In this database a record representing each Customer is stored in the Customers table.  Each customer is identified by a CustomerID.  This field is indexed and unique, as described previously Such a field is called the primary key of the table.  Every order that exists in the Orders table must be connected to a customer that exists in the Customers table.  A copy of the primary key which identifies the customer is stored in the Orders table.  Referential Integrity simply means that:

  1. The database will not allow an order to be stored unless it it contains a CustomerID that exists in the Customers table.
  2. The database will not allow a customer to be deleted if that customer has any orders in the Orders table, and
  3. The customer's CustomerID can not be changed, unless it is also changed to the same value in all of the orders belonging to that customer.

All of the other tables in the Northwind database use this important technique.  Databases that don't apply this concept almost certainly contain data that is no longer meaningful.  For example, in the Northwind database orders could exist that are not connected to a valid customer and/or supplier and/or employee. 

 

If your database application does not enforce referential integrity, then there is undoubtedly incorrect, disconnected data that will cost your business time and moneyFixing the data without fixing the underlying problem is a stop-gap measure that allows garbage data back into the system.  Applying referential integrity to appropriate tables in the database provides a permanent fix. The garbage data will not occur again.  Let me do this for you.


Normalizing:

"Normalization" is another database term that also has significant business ramifications.   Failure to normalize the data in a database is the second most critical mistake I find in 'home grown' database applications.

 

From a business standpoint normalizing a database is important because it eliminates redundant and inconsistent data.  There are five levels, or 'forms', of normalization.  For most databases applying the first three or four 'forms' is sufficient.  If you are really interested in a technical definition feel free to read this description of normalization.

 

To protect your business data, make sure that whoever designs your database understands and can apply data normalization techniques.  Otherwise, the redundant and inconsistent data will cost your business time and money

Databases that are not normalized can be fixed.  Let me do this for  you.

 

Your database application will provide more accurate
information, will perform better, and will grow with your business when
the above techniques are applied.  Let me do this for you.

 



Client/Server Architecture

In addition to the database design techniques previously described, how your application is deployed can also have a significant impact on it's performance and usefulness.  A Client/Server application deployed over a network can provide benefits over a stand-alone system, when done properly. 
 

The following diagrams depict a traditional intra-company, or in-house, network.  But today the Internet can be the network.  Your applications can work with 'The Cloud', that is, they can work with a database hosted on a web server via the Internet.

 

Most of us are aware that computers can do much more when connected to a network.  A network an be as simple as two computers, or as complex as the World Wide Web.  Even a small business application can benefit by being deployed on a network, rather than on a stand-alone computer. There are many ways to deploy applications over a network, and some ways work better than others.

 

A Client/Server setup, in which each computer on the network is either a client or a server, can be particularly useful. Servers are usually more powerful computers dedicated to managing disk drives, printers, or network traffic. Clients are less powerful computers on which users run applications.  These applications rely on the server to perform operations that would overwhelm the client.

A Typical Client/Server Network

Access and SQL Server Database Design


One way to Implement a Microsoft Access Client/Server Application

Access Database Design

 

The most obvious way to implement a Microsoft Access application on a network would be to place the entire application on the Server.  Each Client would be setup to run the application on the Server.  While this is the simplest setup, it is also the least performant.  The amount of network traffic is greatly increased because the result of every query, the display of every form, and each generated report must be transmitted over the network.  The ability of Microsoft Access to handle multiple users at the same time is reduced in this scenario.


A Better way to Implement a Microsoft Access Client/Server Application

Access and SQL Server Database Design

 

This scenario is better.  Only the Access tables are placed on the server.  The forms, reports and queries are on the users' computers. In this case, the Access database's performance is faster because only data is sent across the network. Also, the ability of Microsoft Access to handle multiple users at the same time is improved in this configuration.


Upgrading from Access to SQL/Server

Access and SQL Server Database Design

The next step in improving performance is to replace the Microsoft Access database on the Server with a SQL/Server database.  SQL/Server is designed to meet the data storage and analysis needs of the largest Websites and enterprise data processing systems.  Storing the data tables on the server and running the queries (also called Stored Procedures) on the server dramatically improves the performance of the application. 


Upgrading the Client to Visual Basic or VB.Net and SSRS

Access and SQL Server Database Design

 

The final step to upgrading would be to completely remove Access from the application.  Applications developed with SQL/Server, Visual Basic, VB.Net or C#.Net, and SSRS are extremely flexible, portable over the Web and perform to the highest standards.


In Summary: If you want your database application to be
 deployed in a manner which promotes maximum performance, and which
will allow it to grow with your business let me show you how and then do it for you.