Small but Comfortable Steps
I can do a lot for Bob. He had already discovered the concept of the primary key; thus, he enforced entity integrity by using the auto-number property with MemberID. I can help him maintain domain integrity by creating as many reference or lookup tables as necessary and relating them to the main Members table. In so doing, I can enforce good naming standards on the lookup tables and index the Members table's foreign keys to improve performance. (You'll find additional information about these database concepts in the articles listed in the Related Reading box.)
Bob needs a lookup table to manage the mailings. If a member wanted to receive organization information by some method not already listed in the database, Bob would have to add a new column to the Members table. Since Bob is the only person using this database, adding a column to a table isn't a problem for him. However, if Bob were to hire an assistant and had to share the database, he would realize that any time he wanted to make a change to the underlying table structure he would run the risk of disrupting his assistant's work. A best-practices change would be to create a reference or lookup table, which Web Figure 2 shows, and relate it to a modified version of the Members table, which Figure 1 shows.
I can review the data in the Members table and, where appropriate, add constraints such as the current date and time for the field Date Renewed. I can also add an input mask to help with data entry for the fields Res Zip Code and Mail Zip Code. These constraints also help to enforce domain integrity and simple business-rules integrity. I can make suggestions regarding data types and help Bob deal with the changes if he decides to follow my advice. I can help Bob optimize his queries and create new ones. I can create schemes to find duplicate data and show him how to research and remedy duplicate data problems. I can create data entry forms for Bob, thus making his job of adding and updating members much easier than when he was using the gridlike table. I can help Bob develop techniques for using Access as a Mail Merge data source in Microsoft Word and Microsoft Publisher. I can show Bob how to link Excel to the Members table and use that application to create reports that look like spreadsheets for various organizational units scattered around the world, as Web Figure 3 shows.
Working with Bob has been an interesting learning experience. I've been able to provide him with a good return on his investment, even though his database violates the rules and regulations that so tightly govern relational databases. Bob and I are slowly building a trust relationship, and maybe in time I'll be able to bring his membership database into compliance with best practices in the relational database world. Here's hoping!
End of Article
Prev. page
1
[2]
next page -->
