In the Real WorldDatabase Strategy and Table Tactics

In the Real World Database Strategy and Table Tactics

In warfare, strategy defines the objective; tactics specify the battlefield methods to achieve the strategic objective. Carl von Clauswitz' On War is the seminal 19th century study of strategy and tactics of modern warfare. Niccolo Machiavelli's The Art of War and Che Guevera's On Guerilla Warfare provide earlier and later takes, respectively, on the subject. Designing strategic databases and laying out the tables that comprise the database shouldn't but often does involve open or guerilla hostilities between the participants.

As a database and table designer, try to remain on the side of your product's consumers; you might win a battle or two, but the consumers (users) ultimately will win the war. This is especially true when the consumers control your database implementation budget. Interview database users to determine their workflow and what information is important to the activities for which they're responsible. Keep your consumers in the loop as you develop the database structure. Provide users with periodic copies of the data dictionary or a printed copy of the Relationships table as you make database and table design changes. It's a good idea, however, to minimize the amount of detail in the Documenter reports you send to others involved in the database design process.

Table and field naming is an important database design tactic. There are several schools of thought on naming conventions for tables and fields, but most Access developers agree on one rule: Don't add spaces to table or field names. Northwind.mdb contains only one table, Order Details, with a space in its name, and none of the current Northwind.mdb tables have spaces in field names. Despite the fact that SQL Server 7.0 and later accommodate this dubious feature introduced by Access 1.0, don't use spaces in database, table, or field names. When you export tables or queries to XML documents, spaces in field or table names result in ugly element names, such as <Order_x0020_Details>, in which _x0020_ replaces the space. Use a mixture of upper- and lower-case letters to make names more readable.

Many developers use "tbl" as a table name prefix for consistency with other Access object type identification prefixes, such as "frm" for forms, "dap" for pages, and "rpt" for reports. Using a table object identifier prefix is uncommon in real-world production databases, because tables are the core objects of a database. Using table names that identify the source document or object of the table, such as "Orders", "Invoices", "Products", and the like is a good database design practice. Use plural nouns when naming tables, because tables contain multiple instances of the objects they represent.

Some database administrators (DBAs) use a short prefix for field names that identify the table that contains the fields. A field name prefix indicates to developers the source of the field without the necessity of having to refer to a database diagram or a table field list. Another benefit of adding a prefix based on a table name is avoidance of duplicate names in primary-key and foreign-key pairs. If you choose this approach, a logical prefix for the fields of the HRActions table would be "hra."

Some Access developers add a prefix to field names such as "dat" (for Date/Time) or "txt" (for Text) that specifies the Jet data type, following the generally accepted convention for adding a data type prefix to VBA variable and constant names. This practice is becoming less common as more developers work interchangeably with Jet and MSDE/SQL Server databases. Corresponding Jet and SQL Server data type names aren't necessarily the same; conflicts between Jet-based prefixes and SQL Server prefixes can cause confusion when upsizing Jet databases to SQL Server 2000.