Part I: Access Building Blocks
40
Designing tables
Designing a table is a multistep process. By following the steps in order, your table design can be
created readily and with minimal effort:
continued
Although simple names like Contacts and Orders are adequate, as a database grows in size and com-
plexity, you might be confused about which object a particular name refers to. For example, later in this
book, you’ll read about manipulating database objects through code and macros. When working with
Visual Basic for Applications (VBA), the programming language built into Access, there must be no
ambiguity or confusion between referenced objects. Having both a form and a report named Contacts
might be confusing to you and your code.
The simplest naming convention is to prefix object names with a three- or four-character string indicat-
ing the type of object carrying the name. Using this convention, tables are prefixed with tbl and que-
ries with qry. The generally accepted prefixes for forms, reports, macros, and modules are frm, rpt,
mcr, and bas or mod, respectively.
In this book, most compound object names appear in mixed case: tblBookOrders, tblBookOrder-
Details, and so on. Most people find mixed-case names easier to read and remember than names that
appear in all-uppercase or all-lowercase characters (such as TBLBOOKORDERDETAILS or tblbookor-
derdetails).
Also, at times, I use informal references for database objects. For example, the formal name of the table
containing contact information in the previous examples is tblContacts. An informal reference to
this table might be “the Contacts table.”
In most cases, your users never see the formal names of database objects. One of your challenges as an
application developer is to provide a seamless user interface that hides all data-management and data-
storage entities that support the user interface. You can easily control the text that appears in the title
bars and surfaces of the forms, reports, and other user-interface components to hide the actual names
of the data structures and interface constituents.
Take advantage of the long object names that Access permits to give your tables, queries, forms, and
reports descriptive, informative names. There is no reason why you should confine a table name to
ConInfo when tblContactInformation is handled just as easily and is much easier to understand.
Descriptive names can be carried to an extreme, of course. There’s no point in naming a form frmUp-
dateContactInformation if frmUpdateInfo does just as well. Long names are more easily mis-
spelled or misread than shorter names, so use your best judgment when assigning names.
Finally, although Access lets you use spaces in database object names, you should avoid spaces at all
costs. Spaces don’t add to readability and can cause major headaches, particularly when upsizing to
client/server environments or using OLE automation with other applications. Even if you don’t antici-
pate extending your Access applications to client/server or incorporating OLE or DDE automation into
your applications, get into the habit of not using spaces in object names.
