Chapter 3: Designing Bulletproof Databases
123
FIGURE 3.17
The Join Properties dialog box, used to set up the join properties between tblCus-
tomers and tblSales. Notice that it specifies all records from the Customers table.Given the join properties shown in Figure 3.17, any time the customers and sales tables are
involved in a query, all the customer records are returned, even if a customer has not yet placed
any orders. This setting ought to give a more complete impression of the company’s customer base
instead of restricting the returned records to customers who’ve placed orders.Establishing a join type for every relationship in your database is not absolutely necessary. In the
following chapters, you’ll see that you can specify outer joins for each query in your application.
Many developers choose to use the default equi-join for all the relationships in their databases, and
to adjust the join properties on each query to yield the desired results.Enforcing referential integrity
After using the Edit Relationships dialog box to specify the relationship, verify the table and related
fields, and specify the type of join between the tables, you should set referential integrity between
the tables. Select the Enforce Referential Integrity check box in the lower portion of the Edit
Relationships dialog box to indicate that you want Access to enforce the referential integrity rules
on the relationship between the tables.Caution
If you choose not to enforce referential integrity, you can add new records, change key fields, or delete related
records without warnings about referential integrity violations — thus, making it possible to change critical
fields and damaging the application’s data. With no integrity active, you can create tables that have orphans
(Sales without a Contact). With normal operations (such as data entry or changing information), referential
integrity rules should be enforced.
Enforcing referential integrity also enables two other options (cascading updates and cascading
deletes) that you may find useful. These options are near the bottom of the Edit Relationships dia-
log box (refer to Figure 3.15).