Microsoft Access Programmer Stockton, California

Call MS Access Solutions at (323) 285-0939 for your FREE consultation.

The material below originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Developmentand is reprinted here with the author's permission. There may be references to Figures that are not reprintable and are not used on this page

Using Microsoft Access Rules

Denormalization - Purposely Violating the Rules

Although the developer's goal is normalization, often it makes sense to deviate from normal forms. We refer to this process as denormalization. The primary reason for applying denormalization is to enhance performance.

An example of when denormalization might be the preferred tactic could involve an open invoices table and a summarized accounting table. It might be impractical to calculate summarized accounting information for a customer when we need it. Instead, you can maintain the summary calculations in a summarized accounting table so that you can easily retrieve them as needed. Although the upside of this scenario is improved performance, the downside is that you must update the summary table whenever you make changes to the open invoices. This imposes a definite trade-off between performance and maintainability. You must decide whether the trade-off is worthwhile.

Integrity Rules

Although integrity rules are not part of normal forms, they are definitely part of the database design process. Integrity rules are broken into two categories. They include overall integrity rules and database-specific integrity rules.

Overall Rules

The two types of overall integrity rules are referential integrity rules and entity integrity rules. Referential integrity rules dictate that a database does not contain orphan foreign key values. This means:

• Child rows cannot be added for parent rows that do not exist. In other words, an order cannot be added for a nonexistent customer. . . A primary key value cannot be modified if the value is used as a foreign key in a child table. This means that a CustomerID in the customers table cannot be changed if the orders table contains rows with that CustomerID.


• A parent row cannot be deleted if child rows are found with that foreign key value. For example, a customer cannot be deleted if the customer has orders in the order table.

Entity integrity dictates that the primary key value cannot be Null. This rule applies not only to single-column primary keys, but also to multi-column primary keys. In fact, in a multi-column primary key, no field in the primary key can be Null. This makes sense because, if any part of the primary key can be Null, the primary key can no longer act as a unique identifier for the row. Fortunately, the Access Database Engine (Access 2007's new version of the JET database engine, available with the new ACCDB file format) does not allow a field in a primary key to be Null.

Database Specific Rules

The other set of rules applied to a database are not applicable to all databases but are, instead, dictated by business rules that apply to a specific application. Database-specific rules are as important as overall integrity rules. They ensure that only valid data is entered into a database. An example of a database-specific integrity rule is that the delivery date for an order must fall after the order date.

---

Author Attribution

The preceding originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Developmentand is reprinted here with the author's permission.

---

Get The Best Microsoft Access Programmer Services In Stockton, CA

When you need a Microsoft Access programmer for your Stockton, California business, call MS Access Solutions at (323) 285-0939. We have over 25 years experience in Microsoft Access programmer services. We create Access database applications for all industries and niches including, but not limited to, hospitals, local state and federal government agencies, the U.S. military, universities colleges and school districes, landscaping and agriculture, human resources departments and human resources agencies, and insurance carriers.

We have experience developing large complex Microsoft Access with SQL Server database applications. We also work on smaller projects for our busienss clients; examples are fixing damaged Microsoft Access database forms, redesigning MS Access reports, repairing or replacing broken Microsoft Access macros, and re-programming Visual Basic for Applications (VBA) code.

---

More Information About MS Access Solutions

Get more information about our programming services on the Microsoft Access Programmer Chicago, Illinois web page.