Section 5.4. Relationship Practice | Access 2007[c] The Missing Manual

5.4. Relationship Practice

Every database designer needs to see the world in terms of tables and relationships. Savvy database designers can quickly assess information and see how it's related . With this ability, they can build just the right database for any situation.

The following sections provide two scenarios that help you practice more realistic relationship building. Both databases used in these scenarios are available with the samples for this chapter, and they'll turn up again in the following chapters, when you start to build more sophisticated database objects like queries, reports , and forms.

FREQUENTLY ASKED QUESTION
Dealing with Many-to-Many Relationships

Which approach is better: junction tables or multi-value fields ?

Most database purists will stick with junction tables for years to come. They're accepted, established, and don't hide your database's inner workings. Junction tables are particularly useful if you want to add extra bits of information about the relationship between these two tables. Suppose you create a Students_Classes table to keep track of the classes every student's taking at a popular school. In the Students_Classes table, you could insert additional fields like EnrollmentDate, ConfirmationLetterSentDate, and PrerequisitesChecked.

On the other hand, junction tables have a downsideyou can't work with them as easily in the datasheet. If your data-base uses the Authors_Books junction table, then you need to edit at least two tables just to add one new book to your system. First, you need to insert a record into the Books table. Then, you need to open the Authors_Books table, and add a new record there that maps this book to an author. (You can use lookups in the Authors_Books table to make this process easy, but it still requires a separate step.) But if the Books table includes a multi-value Authors field, then you can add the book and assign the authors in one step, which is more convenient .

If you've decided to go with junction tables and you want to make your life a bit easier, then Access has a great solution. You can build a customized form that deals with more than one table at once. You can create a form that lets the person using the database insert a record in the Books and Authors_Books table at the same time. Best of all, your form can make it look like there's only one table involved. You'll learn how to pull this trick off in Part Four.

5.4.1. The Music School

Cacophon Studios runs a medium- sized music school. They have a fixed series of courses in mind, and a roster of teachers that can fill in for most of them. They also have a long list of past and potential customers. Last year, a small catastrophe happened when 273 students were crammed into the same class and no teacher was assigned to teach it. (Next door, a class of 14 had somehow ended up with three instructors.) They're hoping that Access can help them avoid the same embarrassment this time around.

Tip: Want to play along with Cacophon Studios? Try to pick out possible tables and their relationships before reading ahead.

5.4.1.1. Identifying the tables

Every business is a little different, and it would take a long, detailed analysis to get the perfect table structure for Cacophon Studios. However, even without knowing that much, you can pick out some fairly obvious candidates:

Teachers . A table to store a list of all the teachers on their roster, complete with contact information.
Students . A table to store all class-goers past, present, and potential. You don't need to distinguish between these different groups of people in the Students tableinstead, you can sort out the current students from the others by looking for related data (namely, their class enrollments). So you can keep things simple in the Students table, and just store name and contact information.
Classes . A table to store the classes that Cacophon Studios is running. This table should include the class name, date it starts, date it ends, maximum enrollment number, and any other key information.

Note: Course requirements are stored using a multi-value lookup field named PreviousClassRequirements. This field contains the ID values of each required class. (In other words, every record in the Classes table has the nifty ability to point to other classes in the same table.)

Cacophon Studios will certainly want many more tables before long. But these tables are enough to get started.

5.4.1.2. Identifying the relationships

It's fairly easy to pick out the relationships you need. Students take classes. Teachers teach classes. This suggests two relationshipsone between Students and Classes, and one between Teachers and Classes.

But there's a bit of a hitch. Cacophon Studios certainly doesn't want to stop a single student from taking more than one class, so you'll need a many-to-many relationship between the two tables. And even though Cacophon Studios plans to have only one teacher in each class, they want to keep open the possibility that two or more teachers might co-teach. So Teachers and Classes are also locked in a more complex many-to-many relationship. To support these two relationships, you can create two junction tables, named Students_Classes and Teachers_Classes (respectively).

Figure 5-18 shows a snapshot of this arrangement.

Figure 5-18. Two many-to-many relationships form the basis of the Cacophon Studios music school.

Note: Each record in the Students_Classes table represents a student enrollment in a class. You may want to add some additional fields to Students_Classes to track information like the enrollment date, an enrollment discount you might have offered for early booking, and so on.

5.4.1.3. Getting more detailed

Cacophon Studios is off to the right start, but there's a lot more they still need to think about. First of all, each time they offer a class, they need to create a separate record in the Classes table. This method makes sense, but it causes a potential problem. That's because when a class (like Electro-Acoustic Gamelan) ends, it's usually offered again in a new session, with new students. Although this is a whole new class, it has some information in common with the previous class, like the description, fee, course requirements, and so on.

To deal with this requirement, you need to create another table, named ClassDescriptions. The ClassDescriptions record should have all the descriptive information for a class. The Classes record represents a single, scheduled session of a particular class. That way, the school can offer the same class multiple times with-out confusion.

To make this design work, each record in Classes links to a single record in ClassDescriptions. There's a one-to-many relationship between ClassDescriptions and Classes (Figure 5-19).

Figure 5-19. Thanks to the ClassDescriptions table, you can use the same description for several classes, thereby avoiding redundant data.

Cacophon Studios also needs to think about the sticky financial side of things. Each time they put a student in a class, they need to collect a set fee. Each time they assign a teacher to a class, they need to pay up.

Two tables can fill in these details: TeacherPayments and StudentCharges. Obviously, these tables need relationshipsbut maybe not the ones you expect. You may assume that you should link the StudentCharges record directly to the records in the Students table. That linking makes sense, because you need to know which student owes money. However, it's also important to keep track of what the money's fornamely, the class that the student's paying for. In other words, every record in StudentCharges needs to link to both the Students and the Classes table.

But there's an easier approach. You can save some effort by linking the StudentCharges table directly to the Students_Classes table. Remember, each record in Students_Classes has the student and class information for one enrollment. Every time you add a record in Students_Classes, you need to add a corresponding charge in StudentCharges. One record in the Students_Classes table should link to exactly one record in the StudentCharges table. A similar relationship exists between the Teachers_Classes and TeacherPayments tables. Figure 5-20 shows the whole shebang (not including the ClassDescriptions table shown in Figure 5-19).

Figure 5-20. Every assigned class results in a payment in the TeacherPayments table (top left). Every enrollment results in a charge in StudentCharges (top right). Although this picture's a bit intimidating at first glance, you should be able to work your way through all the tables and relationships one by one. When building a database, it's easiest to start with a few tables, and then keep adding on.

Note: Remember, to create a one-to-one relationship, you need to use a primary key or an index that doesn't allow duplicates (Section 4.1.3). In this example, you need to add a no-duplicates index to the Student_ClassesID field in the StudentCharges table, and the Teacher_ClassesID field in the Teacher-Payments table. These indexes make sure that students get charged only once for each class they take, and teachers get only a single payment for each class they teach.

This database has quickly become quite sophisticated. And Cacophon Studios probably isn't done yet. (For example, it'll more than likely want a table to track student payments.) As with most realistic databases, you can keep adding on new tables and relationships endlessly.

5.4.2. The Chocolate Store

A sales database that stores the products, customers, and orders for a company that sells something is one of the most common databases. In fact, this pattern turns up so often that it's worth looking at a quick example. As you'll see, there are a few basic principles that apply to every sales-driven business, whether the business is selling collectible books or discount pharmaceuticals .

FREQUENTLY ASKED QUESTION
Printing Your Relationship

Why is the Office button Print command disabled when Im looking at the Relationships tab ?

Once you've created your relationships, you might want to have a printed copy at your fingertips. You can't print the contents of the Relationships tab directly, but you can convert it into a report, which is a specialized database object that lets you create a printout whenever you want. (You'll learn how to create reports in Part Three.)

To create a report for your relationships, first arrange all the tables to your liking in the Relationships tab. Then, choose Relationship Tools Design Tools Relationship Report. A preview window appears, which looks more or less the same as the current contents of the Relationships tab. You can then choose Office button Print to send it to the printer.

When you close the relationship report, Access asks you if you want to save it permanently in your database. Usually, you won't bother, because you can easily regenerate the report whenever you need it. However, if you have a complex database and you want to print several different diagrams (each showing a different group of relationships), you may decide to save your relationship report for later use. You'll learn more about reports in Chapter 10.

In this example, you'll meet Boutique Fudge, a mail-order company that serves decadent treats to a large audience of chocolate-crazed customers. Their daring chefs are always innovating , and they need a better way to manage their ever-growing catalog of chocolate goodness. They also need a way to keep track of customers and the orders they make.

5.4.2.1. The product catalog and customer list

Even though you don't know much about Boutique Fudge, you can already think of a few key tables that it'll need. In order to put anything up for sale, they should have the following tables:

Products lists the sinful chocolate delicacies they have for sale. This table records the name, description, and price of each item available. A few optional details also make sensefor example, why not keep track of the current stock using two numeric fields (UnitsInStock and UnitsOnOrder) and a Yes/No field (named Discontinued) to identify products that aren't available any longer?

Note: In many databases, you can't delete old information. A company like Boutique Fudge can't simply delete old products from their catalogs, because these products might be linked to old orders. Also, it makes sense to keep historical information to allow data analysis. (Boutique Fudge could use a query to uncover the top selling products in 1999, and check if declining cocoa levels are linked to lessening sales.) For this reason, you need tricks like the Discontinued field. When you list the products for sale, you can leave out all the discontinued ones, using the filtering skills you picked up in Section 3.2.2.
ProductCategories splits products into a few descriptive groups. That way, customers can browse just the products in the category they want (whether it's Beverages, Candies, Chocolate, or Personalized Choco-wear).
Customers holds the list of chocoholics that have signed up to make an order. You need all the customary information here, like customer names , shipping information, and billing information.

Note: Many companies let customers supply multiple shipping addresses and credit cards. If you allow this flexibility, then you'll need (surprise) more tables. You could create a table of CustomerCreditCards. Every record in Customers could then link to one or more records in CustomerCreditCards. Boutique-Fudge takes the easy way out, and stores a customer credit card and address directly in the Customers table.

So far, there's only one relationship at work: a one-to-many relationship between ProductCategories and Products. Figure 5-21 shows this design.

Figure 5-21. A product (like Chocolate Jasmine Tea) can be placed in one category (like Beverages), but a single category holds many products.

5.4.2.2. Ordering products

It doesn't matter how fancy your sales database isif it doesn't have a way for customers to order the items they're interested in, then Boutique Fudge will run out of money fast.

Database newbies often make the mistake of assuming that they can use one table to store order information. In truth, you need two:

Orders records each order a customer places. It links to the customer who made the order, and adds information like the date the order was placed.
OrderDetails lists the individual items in an order. Each record in the OrderDetails table includes the ID of the product that was ordered, the number of units ordered, and the price at which they were ordered.

Because the average order includes more than one item, a single record in the Orders table is usually linked to multiple records in the OrderDetails table (as shown in Figure 5-22). This setup may sound a bit awkward (because it means you'll need to create a batch of new records for just one order), but the process doesn't have to be that difficult. Access has two features that help out: the subdatasheet feature you've already seen (Figure 5-23) and the forms feature (Chapter 12).

Figure 5-22. Every order can hold an unlimited number of order items. This ability makes Boutique Fudge happy.

Figure 5-23. Thanks to the subdatasheet feature (Section 5.2.4), you can add an order record and the linked order items in the same place.

Notice that the OrderDetails record stores the price of each ordered item. This system may seem to violate the redundant data rule. After all, the product prices are always available in the Products table. However, product prices change, and companies offer discounts . For those reasons, it's absolutely essential that you keep track of the price of an item when it was ordered. Otherwise, you'll have to guess how much each customer owes you.

Note: Database nerds call this sort of information point-in-time data , because it varies over time.

You should also notice that the Order record doesn't store the total cost of the order. That's because the total cost is simply the sum of all the ordered items. If you stored a total cost, you'd open up the possibility of inconsistent datain other words, you've got a problem if the order total you store doesn't match the cost of all the items.

You still have more work to do before Boutique Fudge can become a true database-driven company. For example, you'll probably need to create a Shipments table that tracks orders that it's mailed and a Payments table that makes sure customers pay up. Conceptually, there's nothing new here, but the more tables you add, the more complex your databases become. Now that you know the basics of relationships and good table design, you can stay cool under the pressure.