Access 2010: The Missing Manual

Six Principles of Database Design

With great power comes great responsibility. As a database designer, it’s up to you to craft a set of properly structured tables. If you get it right, you’ll save yourself a lot of work in the future. Well-designed databases are easy to enhance, simpler to work with, and lead to far fewer mind-bending problems when you need to extract information.

Up To Speed: Why It’s Important to Be Unique

You won’t completely understand why it’s so important for each record to have a unique ID number until you work with the more advanced examples in later chapters. However, one of the reasons is that other programs that use your database need to identify a record unambiguously.

To understand why there’s a problem, imagine that you’ve built a program for editing the Dolls table. This program starts by retrieving a list of all your table’s bobbleheads. It displays this list to the person using the program and lets her make changes. Here’s the catch—if a change is made, the program needs to be able to apply the change to the corresponding record in the database. And to apply the change, it needs some unique piece of information that it can use to locate the record. If you’ve followed the best design practices described above, the unique “locator” is the bobblehead’s ID.

Sadly, there’s no recipe for a perfect database. Instead, a number of recommendations can guide you on the way. In the following sections, you’ll learn about a few of ...