Microsoft Office Tutorials and References
In Depth Information
The HealthSK field replaces the HealthPlan field as a foreign key in the Employee table,
and the EmployeeSK field replaces the EmployeeNum field in the Employment table.
When you change an incorrectly entered EmployeeNum field value in the Employee
table, you don’t need to cascade the change to the Employment table. When you change
an incorrectly entered HealthPlan field value in the HealthBenefits table, you don’t have
to cascade the change to the Employee table.
As you design a database, you should not consider the use of surrogate keys, and you
should use an artificial key only for the rare table that has duplicate records. At the point
when you implement a database, you might choose to use artificial and surrogate keys,
but be aware that database experts debate their use and effectiveness. Some of the trade-
offs between natural and surrogate keys that you need to consider are:
• Use surrogate keys to avoid cascading updates to foreign key values. Surrogate keys can
also replace lengthier foreign keys when those foreign keys reference composite fields.
• You don’t need a surrogate key for a table whose primary key is not used as a foreign
key in another table, because cascading updates is not an issue.
• Tables with surrogate keys require more joins than do tables with natural keys. For
example, if you need to know all employees with a HealthPlan field value of A, the sur-
rogate key in Figure A-20 requires that you join the Employee and HealthBenefits tables
to answer the question. Using natural keys as shown in Figure A-17, the HealthPlan
field appears in the Employee table, so no join is necessary.
• Although surrogate keys are meant to be hidden from users, they cannot be hidden from
users who create SQL statements and use other ad hoc tools.
• Because you need a unique index for the natural key and a unique index for the surro-
gate key, your database size is larger and index maintenance takes more time when you
use a surrogate key. On the other hand, a foreign key using a surrogate key is usually
smaller than a foreign key using a natural key, especially when the natural key is a com-
posite key, so those indexes are smaller and faster to access for lookups and joins.
Microsoft Access Naming Conventions
In the early 1980s, Microsoft’s Charles Simonyi introduced an identifier naming conven-
tion that became known as Hungarian notation. Microsoft and other companies use this
naming convention for variable, control, and other object naming in Basic, Visual Basic,
and other programming languages. When Access was introduced in the early 1990s, Stan
Leszynski and Greg Reddick adapted Hungarian notation for Microsoft Access databases;
their guidelines became known as the Leszynski/Reddick naming conventions. In recent
years, the Leszynski naming conventions, the Reddick naming conventions, and other
naming conventions have been published. Individuals and companies have created their
own Access naming conventions, but many are based on the Leszynski/Reddick naming
conventions, as are the naming conventions covered in this section.