Learning About Database Design

You will not become a professional database designer by reading the next three chapters. Instead, you will gain an overall understanding of how relational databases work, which you can call upon when faced with database design issues. The chapters should help you use Access more effectively, enable you to ask the right questions of database professionals, and allow you to design some simple databases on your own.

I take you through the steps of creating a relational database that has data on classic television series such as I Love Lucy and All in the Family. I chose a somewhat frivolous topic for two reasons. Unlike most areas in this book, database design is not a subject that lends itself as much to hands-on, now-you-try-it examples where I can keep you engaged and have you learn by doing. I know that my own My Eyes Glaze Over threshold for the sort of concepts you will soon confront is about four pages. I hope to have a better chance of keeping you going through much essential but often less than scintillating material if the database topic is at least fun.

Second, and more important, relational database design is as much art as science. Although there are standard solutions to some database problems, there is no one way to design a database, either from a procedural or a theoretical standpoint. Two database pros can look at the same database and resolve its issues in distinctly different ways.

Many books use well-established relational database models such as order entry (for example, Access's sample database Northwind Traders) to illustrate key concepts. I also make extensive use of the traditional order-entry model. But if you learned how to use Access using Northwind or a database like it, you might already have the order-entry model etched in your mind. In that case, I fear you'll simply glide through the discussion (been there, done that) without gaining a fresh perspective. There also didn't seem much point to simply reworking the material of other database authors.

So although I give examples from the traditional order-entry model to reinforce database concepts you might already have an inkling of, I also offer an artificial database such as classic TV shows for an original, more-art-than-science approach. I think the positives of injecting some fun into a dry technical subject and applying database principles to an untraditional area offset the negatives of using a mock database that might have little practical application.

The following list shows the overall plan for creating the Classic TV database. Tasks 110 are covered in this chapter; tasks 1113 are discussed in Chapter 3, "Understanding Relationships"; and tasks 1416 are covered in Chapter 4, "Establishing Relationships."

1. Prepare a mission statement

2. Develop mission objectives

3. Determine data needs

4. Create a preliminary list of columns

5. Create a list of tables

6. Refine the lists of fields assigned to tables

7. Eliminate calculated fields

8. Eliminate multipart fields

9. Designate primary keys

10. Eliminate multivalue fields

11. Identify one-to-many relationships

12. Identify many-to-many relationships

13. Refine field names

14. Create relationships in the Relationships window

15. Enforce referential integrity

16. Modify referential integrity through "cascade" options

Legacy Databases

The database design procedure I describe is primarily intended to be instructive. It has major "real-world" limitations; it is a learning tool and should be treated as such.

Among its drawbacks is that it assumes that the new database will be created entirely from scratch. That's rarely the case. Both individuals and organizations usually have some type of existing database in place that must be considered when creating a new database. The old database, in the broadest sense of the term, could be hard copy, such as a stack of reports or a bunch of file cards. It could be an "Ask Joe" system, as in "Whenever we need to know something like that, we ask Joe in Accounting." Most commonly, it is a legacy database, a computer-based system that's been around for a few years.

Whenever you create a new database, you want to start with a clean slate, a fresh approach that is not restricted by earlier incarnations of the data. Predecessor databases often have major problems. In fact, the failings of the existing database could be why you're creating a new one.

But legacy and other preceding databases are still valuable: They help you examine the organization's current information requirements. They inform you of the shortcomings in the current information system. And they provide insight into what the organization's future needs might be.

Moreover, legacy databases often command user loyalty even (perhaps especially) when they're about to be replaced. End users do not always embrace change. Inevitably, a new database system will do some things differently than an old one, which long-time employees can find unsettling. The requirements, needs, and attitudes of a database's various audiences are key factors in database design, and it's important to know their likes and dislikes concerning the legacy database.

Putting People First

That brings me to a mundane, squishy, but essential point: Databases are ultimately about helping people. This means that when you create a database for a group of users, especially in a business environment, you have to spend a lot of time talking to them about it. In this important respect, the profession of database design resembles financial auditing. The popular perception is that auditors spend most of their time alone in their offices fidgeting with their calculators among stacks of documents. But auditors will tell you that a 5-minute interview, even (perhaps especially) with an ill-paid clerk, is often more valuable than an hour's worth of vouching receipts. The work of the database designer is similarly people oriented, and strong interviewing skills are crucial.

Creating a database in a corporate setting is beyond my scope. The importance of extensive employee involvement, as well as all the nuts and bolts of creating a database inside and outside the workplace, is described clearly in Michael J. Hernandez's book Database Design for Mere Mortals (Addison-Wesley, 2003). His book is accessible to any reader, and I have relied significantly on his work in preparing this chapter.