Read an Excerpt
Chapter 27
Ten Tips from the Database Nerds
In This Chapter- Cool ideas from that most uncool of all populations
Like ''em or loathe 'em, the technical experts are always with you. Everywhere you turn, you see someone who may know more about technology than you do. These folks sometimes look funny, frequently act strange, and can often seem amazingly disconnected from reality.
In their more lucid moments, though, the technical experts possess some nuggets of wisdom. This chapter is a distillation of good advice that I picked up over the years. Some of it is very focused, while other parts are downright philosophical. Such is life with the technical experts (but you knew that already).
Document As If Your Life Depends on It
Yes, it''s a pain. Yes, it's a bother. Yes, I do it myself (kinda scary when a guy actually listens to his own advice). If you build a database, make sure that you document every little detail about it. Here''s a list of items to start with:
- General information about the database: Include file locations, an explanation of what the database does, and information on how it works.
- Table layouts, including field names, sizes, contents, and sample contents: If some of the data comes from esoteric or temporary sources (like the shipping report that you shred right after data entry), note that fact in the documentation so that people know.
- Report names, an explanation of the information on the report, and lists of who gets a copy of the report when it''s printed: If you need to run some queries before doing a report, document the process(or better yet, get a nerd to help you automate the whole thing). Documenting who receives the report is particularly important. Jot down the job title in the documentation as well as the current person in the position.
- Queries and logic: For every query, provide a detailed explanation of how the query works, especially if it involves multiple tables.
- Miscellaneous details: Provide information such as the backup process and schedule, where back-up tapes are located (you are doing backups, right?), and what to do if the computer isn''t working. If your database runs a particularly important business function, such as accounting, inventory, point-of-sale, or order entry, make sure that some kind of manual process is in place to keep the business going if the computer breaks down — and remember to document the process!
One final thought: Keep the documentation up to date. Every few months, review your documentation to see whether some updates are needed. Documentation is only useful if it''s up to date and if someone other than yourself can understand it. Likewise, make sure you (or your counterparts in the department) know where the documentation is located. If you have an electronic version, keep it backed up and have a printout handy.
Don''t Make Your Fields Way Too Big
When you''re building a table, take a moment to make your text fields the appropriate size for the data you''re keeping in those fields. By default, Access 97 sets up text fields to hold 50 characters — a pretty generous setting, particularly if the field happens to be holding two-letter state abbreviations. Granted, 48 characters of space aren''t anything to write home about, but multiply that space across a table with 100,000 customer addresses in it and you get 4.8MB of storage space that''s very busy holding nothing.
Adjust the field size with the Field Size setting on the General tab in Design view.
Real Numbers Use Number Fields
Use number fields for numbers, not for text pretending to be a number. Computers perceive a difference between the postal code 47201 and the number 47,201. The postal code is stored as a series of five characters that all happen to be digits, but the number is stored as an actual number. You can do math with it (just try that on a postal-code field, sometime) and all kinds of fun stuff.
When choosing the type for a new field with numbers in it, ask yourself a simple question: Are you ever going to make a calculation or do anything math-related with the field? If so, use a number type. If not, store the field as text and go on with your life.
Better Validations Make Better Data
Validations work hand in hand with masks to prevent bad data from getting close to your tables. Validations are easy to make, quick to set up, and ever vigilant (even when you''re so tired you can't see straight). If you aren''t using validations to protect the integrity of your database, you really should start. Flip to Chapter 7 and have a look at the topic.
Use Understandable Names
When building a table or creating a database, think about the names you use. Will you remember what they mean three months from now? Six months from now? Are they intuitive enough for someone else to look at the table and figure out what it does, long after your knowledge of Access 97 puts your career on the fast track?
Now that Windows 95 finally offers long filenames, please use them. You don''t need to get carried away, but now you have no excuse for files called 97Q1bdg5. Using Q1 1997 Budget Rev 5 makes much more sense to everyone involved.
Take Great Care When Deleting
Whenever you''re deleting records from a table, make sure that you're killing the right record, check again, and — only when you''re sure — delete the original. Even then, you can still do a quick Ctrl+Z and recover the little bugger.
Why all the checking and double-checking? Because after you delete a record and do anything else in the table, Access 97 completely forgets about your old record. It''s gone, just as if it never existed. If that record happened to be important and you don''t have a current back-up file, you''re out of luck. Sorry!
Keep Backups
There''s no substitute for a current backup of your data, particularly if the data is vital to your company. Don''t believe me? Let the phrase no receivables float through your mind for a while. How do you feel about backups now? I thought you''d see it my way.
Think First and Then Think Again
Apply this rule to any Access 97 step that contains the word delete or redesign. Think about what you''re doing. Then think again. Software makes handling large amounts of data easier than ever before, but it also offers the tools to screw up your data on a scale not seen since the time of P.T. Barnum.
Thomas Watson, Sr., the president of IBM for years and years, said it best: "Think."
Get Organized and Keep It Simple
Although they may seem different at first blush, these two tips work together to promote classic nerd values like a place for every gadget and my query ran faster than yours, so there. By keeping your computer orderly and organizing your entire workspace, you have everything you need at hand. Get yourself a Barcalounger and a remote control, and you never need to leave the office again.
But you can get too organized. In fact, doing so is altogether too easy. Temper your desire to organize with a passion for doing work with as few steps as possible. On your computer, limit the number of folders and subfolders you use — a maximum of five levels of folders is more than enough for just about anybody. If you go much beyond five levels, your organization starts bumping into your productivity (and nobody likes a productivity loss, least of all the people who come up with those silly little slogans for the corporate feel-good posters).
Know When to Ask for Help
If you''re having trouble with something, swallow your ego and ask for help. Saying I don''t know and then trying to find out holds no shame. This rule is especially important when you're riding herd on thousands of records in a database. Small missteps are magnified and multiplied, so ask for help before the situation becomes dire.
Access 97 For Windows® For Dummies®, Page 100
Input masks work best with short, highly consistent data. Numbers and number/letter combinations that all look alike are excellent candidates. Part numbers, stock-keeping units, postal codes, phone numbers, and Social Security
numbers beg for input masks to ensure that the right data gets into the field.
You create an input mask in one of two ways: You can either type in the mask manually or ask the Input Mask Wizard for some help. As luck would have it, the Input Mask Wizard isn't terribly bright — he only knows about text and date fields. And even then, he offers just a few options to make your life easier. To accomplish anything more means cracking your knuckles and doing it by hand.
Using the Input Mask Wizard
The Input Mask Wizard gleefully helps if you're making a mask for a phone number, Social Security number, United States zip code, or simple date and time field. Beyond those fields, he's clueless, so don't look for his help with anything other than text or time/date type fields.
To ask the wizard's help, go through these steps:
- With the database file open, click on the table you want to work with and then click on Design.
- Click on the name of the field you want to adjust.
- Click on the Input Mask box.
- Click on the Build button at the right side of the Input Mask text box.
The wizard appears, making a glorious entrance just like in Figure 7-6.
You can use the wizard only with text and date fields. Don''t tempt the wizard's wrath by rousing it to work with another kind of field.
If Access 97 complains that the Wizard isn't installed, you didn't include the developer tools option while installing Access 97 (don't fret if you get this error message — I did, too). To fix the problem, get out your original Access 97 or Office 97 CD-ROM and put it in your CD-ROM drive. Close Access 97 and then choose Start—>Settings—>Control Panel. In the Control Panel window, double-click Add/Remove Programs. Follow the prompts to install the Access 97 developer tools. If you're in a...
The table flips into Design view.
The General tab in the Field Properties section (the bottom half of the window) displays the details of the current field.
The cursor hops into the Input Mask box. To the right of the box, a small button with three dots appears. That''s the Build button, which comes into play in the next step.