Microsoft Office Tutorials and References
In Depth Information
Designing a Database
Number and the Product Code or ISBN as the primary key for the table.
Then one order includes one entry for each item purchased.
Viewing a sample order-entry database design
Figure 3-5 shows the new, improved table design for a retail-store system,
with little key icons by the primary key fields.
One other thing: We like to include a Date Last Changed or Date Updated
field in every table. This field almost always turns out to be useful. We omitted
these fields from Figure 3-5 to save space, but we’d include them in the real
Linking your tables
If you end up with only one table, you can skip this step, but that situation
is fairly rare. Almost every database ends up with a second table at the very
least — to contain those pesky codes.
Look at the tables in your database, and see which tables contain fields that
match fields in other tables. Determine whether a one-to-one, one-to-many,
or many-to-many relationship exists between the two tables (as described in
the “What Are Relationships?” section, earlier in this chapter). For each pair
of related tables, you can determine which fields actually relate the tables by
following these guidelines:
✦ One-to-many relationships: Figure out which is the “one” (master) and
which is the “many” (detail) table in this relationship. Make sure that
the detail table has a foreign key field (or fields) to match the primary
key field(s) in the master table. The Customers and Orders tables have a
one-to-many relationship in the bookstore example, because a customer
may have no, one, or many orders. (Okay, someone who has no orders
technically isn’t a customer, but the relationship still counts as one-to-