Microsoft Office Tutorials and References
In Depth Information
Designing a Database
many because she’s a potential customer.) The primary key field in the
master table (Customers) is Customer Number. To relate the tables,
you have to give the Orders table a Customer Number field to be used
as the foreign key.
One-to-one relationships: Make sure that both tables have the same
primary key field(s). (The example shown in Figure 3-5 doesn’t include any
one-to-one relationships.)
Many-to-many relationships: Access can’t store a many-to-many
relationship directly. Set up a junction table containing the primary keys
of the two tables to connect the tables. In the bookstore example, the
Orders and Products tables have a many-to-many relationship: One
order can have many products, and one product can occur in many
orders. The Order Details table is the junction table that contains the
primary key of the Orders table (OrderID) and the primary key of the
Products table (ProductID). This junction table can include additional
information. (The Order Details table, for example, includes the quantity
of the item that’s ordered as well as the price of each item.)
The related fields don’t need to have the same name in the two related
tables, but the types, lengths, and contents of the fields have to match. (We
usually find that giving the two fields the same names is less confusing.
Preserving sanity is also good for business.) Figure 3-5, earlier in this
chapter, shows the relationships among your tables as lines running between the
related fields. For one-to-many relationships, we use 1 at one end of the line
and an infinity symbol (∞) at the “many” end.
Refining your links
The relationships among your tables can be a bit more complex. (What
relationships aren’t?) So you may need to make a few more decisions about how
your table relationships work:
Referential integrity: This nifty feature means that you can tell Access
not to allow a record to exist in a detail table unless it has a matching
record in the master record. If you turn on referential integrity checking
for the relationship between the Customers and States_Provinces tables,
for example, Access won’t allow you to enter a record with a State_
Province code if the code doesn’t exist in the State_Province_
Abbreviation field of the States table. This feature is a “No bogus
codes!” rule that doesn’t require any programming (as you find out in
Book II, Chapter 6).
Cascading updates: Another way-cool Access feature updates detail
records automatically when you change the matching master record. If
you find out that you have the wrong ProductID for an item, and you
Search JabSto ::




Custom Search