Organizing Fields into Tables
| Let's get back to developing the Classic TV database. Table 2.6 shows the current list of fields. The list has been modified from that in Table 2.2 to reflect our bad experience with multiple actor fields in the "one-table-fits-all" solution. Thus, instead of fields for Actor Name #1, Actor Name #2, and so on there is just one Actor Name field. The same is true for Actor Gender, Actor Biography, and Character Name/Occupation.
With the decision to use a relational database instead of the discredited one-table-fits-all solution, you now need to create a list of tables and decide which fields go in which tables. Let's accomplish those objectives. Mission Objectives and Table CreationIt might seem that the most logical approach would be to structure your tables around your mission objectives, dedicating one table to each objective. This method would apparently ensure that the database meets all of your goals. But this is exactly the approach you should not take. Consider the following mission objective from the business world, which applies to most companies: "We need to ensure timely receipt of the funds customers owe us." For most firms, the primary means of accomplishing this objective is the customer invoice. Take a look at the invoice Northwind Traders has prepared for its Save-A-Lot customer (see Figure 2.1). Viewed from top to bottom, you can roughly say that the customer's address comes first, order information (OrderID, salesperson, and so on) comes second, order details (products, quantities, prices, and so on) come third, and summary and freight information comes last. Figure 2.1. A customer invoice requires data with several different subjects. Imagine the breadth and complexity of a table that in every record had data that was as disparate as the address of a customer and the order date of an order. Table 2.7 shows just a few of the fields that table would require. Order #11064 to Save-A-Lot has five products, so you need to repeat the customer contact info five times for that order alone. Imagine if you had a hundred orders with a hundred products in each; you'd have to repeat customer contact info 10,000 times.
You face similar problems if you choose to use the mission objectives for the Classic TV database to create a list of tables. For example, consider the objective "I want to know the program's broadcast history." To accomplish this objective, you need to know program names and the years the program airedin other words, data about programs. You also need data about the networks on which the programs aired. Table 2.8 shows just a few of the fields in the table, but you can see that you would have to repeat the Network Notes for each program.
Don't get me wrong: Mission objectives do play an important role in ensuring that you have included all the data you need to fulfill the organization's mission. You'll want to compare your final list of fields and tables against your mission objectives to make sure you can accomplish them. But you do not want to use mission objectives as a launch point for creating a list of tables. One Subject, One TableYou've seen that mission objectives and the media they require (such as invoices and order forms) are inadequate guides for organizing data into tables. But if you're not going to use these as guideposts, then what do you use? Here's what you need to remember: The key, essential requirement of a table is not that it represent a single objective, but that it define a single subject. That's exactly the problem with using mission objectives: They require you to bring together data about several subjects. The invoice is a good example. Customer contact info (names, address, ZIP codes, and the like) is one subject, order data (order dates, shipped dates) is another, product data (product names, suppliers) is a third, and so on. The data for an invoice will, therefore, not come from a single Invoices table. Instead, it will be drawn from several tablesCustomers, Orders, Products, and so oneach of which has a single subject. Use the Fields to Develop TablesNow the question becomes "How do you develop this list of tables, each of which is dedicated to a single subject?" One of the best ways is to use the fields themselves. That's really not odd or paradoxical. Think about the way you store items in boxes or organize a to-do list or arrange your kitchen drawers. Most likely, you first look at all the stuff you've got. Most things naturally go with related items, and you organize them accordingly into several groups. There are usually a few items you're not sure what to do with. You stick these in the least objectionable place and make a mental note that you might want to move them later. I've forgotten nearly all of my high school chemistry. But one thing I do remember is my chemistry teacher standing over a Bunsen burner telling us to "waft gently" the fumes from whatever experiment we had just conducted. I'd like to ask you to "waft gently" the fields to decide which tables they belong to. In other words, don't extensively analyze the fields, but rather smell them out and get a general sense of which tables they belong in. Later, you'll refine your lists of both tables and fields. With some gentle wafting, you can place most of the fields in the revised field list of Table 2.6 into appropriate tables. The Networks table contains only data about the networks, the Actors table contains only data about the actors, and so on. Assigning Ambiguous FieldsA few fields you're not sure where to put. Year Started and Year Ended seem like they belong with the program, but they also seem somehow connected with the network that broadcast the show. Character roles are also difficult to place. They certainly apply to a specific program, but they are also played by specific actors. You have to make some decisions, but remember that you're making them on only a temporary basis. You'll continue to refine tables and fields as you move through the design process. You come up with the preliminary breakdown of tables and fields shown in Table 2.9.
I think most of the tables and the choice of fields initially assigned to them are fairly straightforward. You might wonder why Networks and Programs are separate tables. If the database was about, say, various types of entertainmentlive theater, horse racing, rock concertsit's possible that a single table named Television might encompass both the programs and networks tables. But in a database about television itself, programs and networks are distinct subjects. The history of a specific network is separate from a particular show that might have been broadcast on it. Later I describe how programs and networks relate to one another, which should reinforce your sense that they are indeed separate subjects. The Genres table, which classifies the program into different categories, also might stand a word of explanation. There were alternatives. You could use just the Synopsis field to provide a description of the program that told you the genre. "Green Acres is a series about a New York lawyer who uproots his wife from their Park Avenue apartment to a pea-size town in the boonies where they routinely get snookered by the locals." That gives you a general synopsis of the show, and you could enter similar descriptions for other programs in the Program Notes field. But this depiction is not helpful if you want to find other shows in your database of the same type. What would you search for? Boonies? Snookered? On the other hand, suppose you assign a specific genre to the show, such as Rural comedy. You could then assign that value to similar programs, such as The Andy Griffith Show and Petticoat Junction, to retrieve comparable shows easily. As you'll see a little later, you can limit the genres used to describe programs to a specific set of values. Limiting the values you can enter might initially seem to make your database less flexible and more restricted. But by controlling the values you enter in a field, you can increase your ability to classify data into specific groups and, ultimately, increase the value of the information your database provides. How to Name TablesI don't think finding suitable table names poses a major challenge. Most of the table names I have used for the Classic TV databasePrograms, Networks, and so onare likely those you would have chosen yourself. Nonetheless, it's worthwhile to state a few guidelines, some of them obvious, for this task. Table names should:
Chapter 5 has more to say about naming tables in an Access database using the Leszynski convention.
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
EAN: 2147483647
Pages: 169