Microsoft Office Tutorials and References
In Depth Information
Designing a Database
change it in the ProductID field in the Products table, you can
configure Access to update the code automatically in the Order Details table.
✦ Cascading deletes: As with cascading updates, this feature deletes detail
records when you delete the master record.
This feature is a bit more dangerous than cascading updates, and you
may not want to use cascading deletes for most related tables. If a
product becomes obsolete, and you stop carrying it, deleting it from
the Products table is a bad idea. Consider: What’s supposed to happen
to all those matching records in the Order Details table (assuming that
you sold some units of that now-obsolete item)? Don’t delete the Order_
Details records, because then the table shows that you never sold those
items. Instead, mark the item as unavailable (in the example shown in
Figure 3-5, set the Discontinued field in the Products table to Yes),
and leave the records in the tables.
Now you have a fully relational database design. The last step is cleaning up
the loose ends.
Seeing what’s in a name
Table names should be plural (such as Products and Customers). Field
names should be singular (such as City and Quantity). Access doesn’t
care, but this system makes things easier for you to read. Don’t use all
capital letters, either; you’ll feel as though Access is yelling at you. We like to
capitalize the first letter of each word.
In this chapter, we use two-word field names with a space between the two
words. Using spaces in names may be a bad idea, however, if you ever want
to link the data from your Access database to a larger database that
misinterprets the space as an end-of-name flag. Also, it means that when you type
formulas, you have to enclose the name in square brackets to tell Access
where the name starts and ends. Who wants to remember to do that? Decide
in advance whether you’re going to use spaces (Product Code),
underscores (Product_Code), or neither (ProductCode) in all your names. You
can’t use a hyphen, because that means subtraction in Access.
Finally, don’t use words that have specialized meanings to Access, including
these words: Name, Date, Word, Value, Table, Field, and Form. You can
actually confuse Access. It’s not a pretty sight.
In general, be consistent, to make it easier to remember table and field
names. (Now, was that table name Product_Code or ProductCode?)