Ms Office Tune Up

Think back to the Dolls table you created in Chapter 1 to store a list of bobblehead dolls. One of the Dolls table’s pieces of information is the Manufacturer field, which lists the name of the company that created each doll. Although this seems like a simple-enough detail, it turns out that to properly assess the value of a bobblehead, you need to know a fair bit more about the manufacturing process. You may want to know things like where the manufacturing company’s located, how long it’s been in business, and if it’s had to fight off lawsuits from angry customers.

If you’re feeling lazy, you could add all this information to the Dolls table, like so (the grayed-out columns are the new ones):

Your first reaction to this table is probably to worry about the clutter of all these fields. But don’t panicin the real world, tables must include all the important details, so they often grow quite wide. (That’s rule #3 of data design, from Section 2.5.3.) So don’t let the clutter bother you. You can use techniques like column hiding (Section 3.1.4) to filter out the fields that don’t interest you.

Although column clutter isn’t a problem, another issue lurks under the surface in this example redundant data. A well-designed table should list only one type of thing. This version of the Dolls table breaks that rule by combining information about the bobblehead and the bobblehead manufacturer.

This situation seems innocent enough, but if you add a few more rows, things don’t look as pretty:

Once you have two bobbleheads that were made by the same company (in this case, MagicPlastic), you’ve introduced duplicate data, the curse of all bad databases. (You’ll recognize this as a violation of rule #4 of good database design, from Section 2.5.4.) The potential problems are endless:

• If MagicPlastic moves its plants from China to South Korea, you’ll need to update a whole batch of bobblehead records. If you were using two tables with related data (as you’ll see next), you’d have just one record to contend with.

• It’s all too easy to update the manufacturer information in one bobblehead record but miss it in another. If you make this mistake, you’ll wind up with inconsistent data in your table, which is even worse than duplicate data. Essentially, your manufacturer information will become worthless because you won’t know which record has the correct details, so you won’t be able to trust anything.

• If you want to track more manufacturer-related information (like a contact number) in your database, you’ll have to update your Dolls table and edit every single record. Your family may not see you for several few weeks.

• If you want to get information about manufacturers (but not dolls), you’re out of luck. For example, you can’t print out a list of all the bobblehead manufacturers in China (at least not easily).

It’s easy to understand the problem. By trying to cram too many details into one spot, this table fuses together information that would best be kept in two separate tables. To fix this design, you need to create two tables that use related data. For example, you could create a Dolls table like this:

And a separate Manufacturers table with the manufacturer-specific details:

This design gives you the flexibility to work with both types of information (dolls and manufacturers) separately. It also removes the risk of duplication. The savings are small in this simple example, but in a table with hundreds or thousands of bobblehead dolls (and far fewer manufacturers), the difference is dramatic.

Now, if MagicPlastic moves to South Korea, you need to update the Location field for only one record, rather than many instances in an overloaded Dolls table. You’ll also have an easier time building queries (Chapter 6) that combine the information in neat and useful ways. (For example, you could find out how much you’ve spent on all your MagicPlastic dolls and compare that with the amounts you’ve spent for dolls made by other manufacturers.)

Note: Access includes a tool that attempts to spot duplicate data in a table and help you pull the fields apart into related tables. (To try it out, choose Database Tools Analyze Analyze Table.) Although it’s a good idea in theory, this tool really isn’t that useful. You’ll do a much better job of spotting duplicate data and creating well designed tables from the start if you understand the duplicate-data problem yourself.

Popularity: 2% [?]