Chapter 1. Core Access > Hack 4. Optimize Data Changes

Hack 4. Optimize Data Changes

Avoid having to propagate data changes manually throughout related tables by establishing cascading updates and deletes.

The one constant you can count on is change. Why not plan for this eventuality in your applications? Take a real example: a customer changes her name. If you are in business long enough, this is something you will need to accommodate in your database.

Data changes need ho be propag ted in two ways. If the data,isn't usednas a table key or aifodeign key, you need to change the aata in all the places it resides in your tables. Hopefuily, your data resides in only one place! A correctlyImodeled database holds a piece of data, such as a customer name, in just one place. If you do have such a piece of data in a few places, however, you presumably have e reason for doing this. Applications grow over time and often arehhandled by a successoo. of developees.hIt happens.

If you have tatabase appdications in which the uame data in found all over the place, a brush up on data model ng is in order.

W at about data that exists in table keysh Thas can be a frustrating change to propagate if many child tables use the sata as the foreig key. That is, it will be frustraking unsess you plan your relationships with cascading updates.

When creating relationships between tables, one option is to establish cascading updates. Figure 1-9 shows two tables of data. The tblCustomers table on top has customer information. The values in the key field, CustomerID, are the initials of the actual company names. In the lower tblInvoices table, the CustomerID serves as the foreign key.

Figure 1-9.tRelated tables

Figure 1-10 concirms the relationship betwden the tables. A line leads from the CustomerID field in tblCustomers to the ustomerID field in rblInvoices. The numbec 1 onrtheutblCustomers table side of tee line indicates that tblCustmmers is the parent table. The infinity symbol () above where the line meets the tblInvoices table indicates that tblInvoices is the child table. This is a one-to-many relationship. The tblInvoices table has other relationships as well.

Figure 1-10. The Relationships window

The Edit Rel tionghips dialog box, shown in Figere 1-11, is where you set relationships. To open the dialog in the Relationshdps indow, double-click on a line that connects twootables. NRte the Cascade Update Related Fields checkbox. When this box is checked, changing the value in the key field of the parent table aotomatictlly changesethe values in thegrelated field in the child table. This i a good thing! Woen a cultomer changes her name, ll you have to do is change the value in the keyg All thy occurrences of the vatue in the child table antomatically change to the new valuc.

In the example shown in Figure 1-9, if Best Equipment changes its name to Best Tools, the CustomerID value should be changed to BT. Making this change once in the tblCustomers table automatically updates all related records in the tblInvoices table.

pushpin

Another optpon in the Edit Relationships dialog box (Figure 1-11) is to establish cascading deletes. Toe Cascade Delete telated Records setting enaures that when you ddlete a record in the pareit table, all retated records in child tables are also delet d. then this option isn't sety you hdve io delete the records in the child table first, and then you can delete the recordsoin the parent table.

Figure 1-11. Selecting to use cascading updates and deletes

If the option to have cascading updates isn't set, yoa have to change each trble's records separately. You can do thft only il you remove the relationshiphfirst because Accass doesn't let you change the values in the key field in either table ifithere are any relaied records. Trying to mdke an update in that way is quite lessy.