Microsoft Office Tutorials and References
In Depth Information
Chapter 6: Relating Your Tables and Protecting Your Data
Chapter 6: Relating Your Tables
and Protecting Your Data
In This Chapter
Creating relationships between tables
Protecting your relationships with referential integrity
Using cascading updates and deletes to protect data integrity
Printing the relationships between tables
Relational database management systems such as Microsoft Access
exist because the real world often requires large amounts of data to
be stored. One-to-many or many-to-many relationships often exist among
pieces of data. Any one customer, for example, may place many orders (a
one-to-many relationship). Any one order may be an order for many
products. In a school, any one student may enroll in many courses. Any one
course has many students enrolled in it.
When information is spread across multiple tables, the data must always
link up correctly. If customer Hortense Higglebottom places an order for five
lawn flamingoes on April 1, the records from the various tables that record
that information must jibe perfectly so that she gets what she ordered and
pays the right amount for what she bought, so that her lawn flamingoes are
sent to the correct address — and so that she doesn’t end up getting 37
golden whistles instead. The technical term for making absolutely sure that
all the pieces line up correctly, at all times, is referential integrity. Before we
get to the specifics of how you enforce referential integrity in your database,
we provide a brief review of all the buzzwords and concepts related to the
whole idea of storing chunks of data in separate tables.
Book I, Chapter 3 describes relationships among tables from a design
perspective.
When two tables are related in a one-to-many relationship, the table on the
“one” side of the relationship must have a primary key field that uniquely
identifies each record. For this reason, the table on the “one” side is often
referred to as the master table. For the customers-and-orders example, the
Address Book table is on the “one” side of the relationship, and the primary
key field, ContactID, has a unique value for each record — that is, each
customer listed in the table has a value in the ContactID field that’s unique
to him. If you want to refer to a customer anywhere else in the database,
you can use that unique ContactID value as a shortcut (see Figure 6-1).
Search JabSto ::




Custom Search