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 shaded 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 panic—in 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.4.) 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 data-bases. (You’ll recognize this as a violation of rule #4 of good database design, from Section 2.5.4.) The potential problems are endless:

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.)

This bobblehead database shows you an example of a relationship. The telltale sign of a relationship is two tables with matching fields. In this case, the tip-off’s the Manufacturer field, which exists in both the Dolls table and the Manufacturers table.

Using these linked fields, you can start with a record in one table and look up related information in the other. Here’s how it works:

In other words, a relationship gives you the flexibility to ask more questions about your data, and get better answers.

In the previous example, the Dolls and Manufacturers tables are linked through the Manufacturer field, which stores the name of the manufacturing company. This seems like a reasonable design—until you spend a couple of minutes thinking about what might go wrong. And databases experts are known for spending entire weeks contemplating inevitable disasters.

Here are two headaches that just may lie in store:

You might recognize these problems, because they’re similar to the challenges you faced when you tackled primary keys (Section 2.4). As you learned, it’s difficult to find information that’s guaranteed to be unique and unchanging. Rather than risk problems, you’re better off just relying instead on an AutoNumber field, which stores an Access-generated ID number.

Interestingly enough, you use the same solution when linking tables. To refer to a record in another table, you shouldn’t use just any piece of information—instead, you should use the unique ID number that points to the right record. Here’s a redesigned Dolls table that gets it right by changing the Manufacturer field to ManufacturerID:

If you take a look back at the Manufacturers table (Section 5.1.1), then you can quickly find out that the manufacturer with the ID value 1 is MagicPlastic.

This design’s the universal standard for databases. However, it does have two obvious drawbacks:

To solve both these problems, use a lookup. Lookups show the corresponding manufacturer information in the Dolls table, and they also let you choose from a list of manufacturers when you add a record or edit the ManufacturerID field. (You saw how to use lookups with value lists in Section 4.4.1. You’ll learn how to use lookups to bring together related tables, like Dolls and Manufacturers, in Section 5.2.5.)

No, this isn’t a detour into feel-good Dr. Phil psychology. Database nerds use the labels parent and child to identify the two tables in a relationship, and keep track of which one’s which.

Here’s the analogy. As you no doubt know, in the real world a parent can have any number of children. However, a child has exactly one set of parents. The same rule works for databases. In the bobblehead database, a single manufacturer record can be linked to any number of doll records. However, each doll record refers to a single manufacturer. So according to the database world’s strange sociology, Manufacturers is a parent table and Dolls is a child table. They’re linked by a parent-child relationship.

It’s important to realize that you can’t swap the parent and child tables around without changing your relationship. It’s incorrect to suggest that Dolls is the parent table and Manufacturers is the child table. You can see that such a suggestion would break the parent-child analogy: a single doll can’t have more than one manufacturer, and a manufacturer isn’t limited to creating a single doll. In order to prevent problems and all-around fuzzy thinking, you need to know exactly which table’s the parent and which one’s the child.

If you have database-savvy friends, you’ll hear the term parent-child relationship quite a bit. The same relationship’s also called a one-to-many relationship (where one is the parent and many represents the children, because a single parent record in one table can link to several child records in the other). It’s the most common relationship, but not the only one—you’ll learn about two other types in Section 5.3 and Section 5.3.2.

You can try out the following steps with the Bobblehead.accdb file, which is included with the online examples for this chapter. It contains the Dolls and Manufacturers tables, in their original form (with no relationships defined). The BobbleheadRelationships.accdb database file shows the final product: two tables with the right relationship.

Here’s what you need to do to set up a relationship:

The next time you want to change or add relationships, you’ll follow the same path to get to the Relationship window (choose Database Tools → Show/Hide → Relationships).

If you choose to save a relationship diagram (in step 11 in the previous section), the tables you added appear automatically, just as you left them. If you want to work with tables that aren’t in any relationships yet, you can add them to the diagram by right-clicking anywhere in the blank area, and then choosing Show Table.

If you chose not to save your relationship diagram, you can use a few shortcuts to put your tables back on display:

As you already know, you can use the Relationships tab to create new relationships. You can also edit the relationships you’ve already created. To do so, right-click the line that represents the relationship, and then choose Edit Relationship. (This takes some nimble finger-clicking. If you don’t see the Edit Relationships option in the menu, you’ve just missed the line.) To remove a relationship, right-click the relationship line, and then choose Delete.

Now that you’ve gone to the work of defining your relationship, it’s time to see what benefits you’ve earned. As in the real world, relationships impose certain restrictions. In the database world, these rules are called referential integrity. Taken together, they ensure that related data’s always consistent.

In the bobblehead example, referential integrity requires that every manufacturer you refer to in the Dolls table must exist in the Manufacturer table. In other words, there can never be a bobblehead record that points to a nonexistent manufacturer. That sort of error could throw the hardiest database software out of whack.

To enforce this rule, Access disallows the following three actions:

Along with these restrictions, Access also won’t let you remove a table if it’s in a relationship. You need to delete the relationship first (using the Relationships window) and then remove the table.

Cascading deletes

The rules of referential integrity stop you cold if you try to delete a parent record (like a manufacturer) that other child records (like dolls) link to. However, there’s another option—and it’s much more drastic. You can choose to blow away all related child records whenever you delete a parent. For example, this would allow you to remove a manufacturer and wipe out all the dolls that were produced by that manufacturer.

Warning

Cascading deletes are risky. It’s all too easy to wipe out way more records that you intend, and if you do there’s no going back. Even worse, the Undo feature can’t help you reverse this change. So proceed with caution.

FREQUENTLY ASKED QUESTION: Switching Off Referential Integrity

Are there any situations where you don’t want to enforce referential integrity?

In most cases, referential integrity’s the ultimate database safety check, and no one wants to do without it—especially if the database includes mission-critical information for your business. Remember, referential integrity prevents only inconsistent data. It still lets you leave a field blank if there’s no related record that you want to link to.

The only time you may decide to dodge the rules of referential integrity is when you’re using partial copies of your database. This situation usually happens in a large business that’s using the same database at different sites.

Consider an extremely successful pastry sales company with six locations. When a customer makes an order at your downtown location, you add a new record in the Orders table, and fill in the CustomerID (which links to a full record in the Customers table). But here’s the problem. The full customer record may not be in your copy of the database—instead, it’s in one of the databases at another site, or at company headquarters. Although the link in the Orders table’s valid, Access assumes you’ve made a mistake because it can’t find the matching customer record.

In this situation, you may choose to turn off referential integrity so you can insert the record. If you do, then be sure to enter the linked value (in this case, the CustomerID) very carefully to avoid errors later on.

To turn on this option, you need to switch on the Cascade Delete Related Records setting when you create your relationship (Figure 5-4). You can also modify the relationship later on to add this setting.

Once you’ve switched this option on, you can try it out by deleting a manufacturer, as shown in Figure 5-6.

Figure 5-6. In this example, the Dolls-Manufacturers relationship uses the Cascade Delete Related Records setting. When you delete a manufacturer, Access warns you that you’ll actually end up deleting every linked doll record, for a total of nine records.

Relationships aren’t just useful for catching mistakes. Relationships also make it easier for you to browse through related data. In Chapter 6, you’ll learn to create search routines that pull together information from related tables (Section 6.3). But even without this technique, Access provides some serious relationship mojo in the datasheet.

Here’s how it works. If you’re looking at a parent table in the datasheet, then you can find the related child records for any parent record by clicking the plus box that’s just at the left of the row (Figure 5-7).

Figure 5-7. Curious to find out what dolls you have from MagicPlastic? Just click the plus box (circled).

This drops a subdatasheet into view, which shows just the related records (Figure 5-8). You can use the subdatasheet to edit the doll records here in exactly the same way as you would in the full Dolls datasheet. You can even add new records.

Figure 5-8. The subdatasheet’s really a filtered version of the ordinary Dolls datasheet. It shows only the records that are linked to the manufacturer you chose. The subdatasheet has all the same view settings (like font, colors, column order) as the datasheet for the related table.

A parent table may be related to more than one child table. In this case, Access gives you a choice of what table you want to use when you click the plus box. Imagine you’ve created a Customers table that’s linked to a child table of customer orders (Orders), and a child table of billing information (Invoices). When you click the plus box, Access doesn’t know which table to choose, so it asks you (see Figure 5-9).

Figure 5-9. When Access doesn’t know which table to use as a subdatasheet, it lets you pick from a list of all your tables. In this case, only two choices make sense. Choose Orders to see the customer’s orders, or Invoices to see the customer’s invoices. When you select the appropriate table in the list, Access automatically fills in the linked fields in the boxes at the bottom of the window. You can then click OK to continue.

As you create more elaborate databases, you’ll find that your tables are linked together in a chain of relationships. One parent table might be linked to a child table, which is itself the parent of another table, and so on. This complexity doesn’t faze Access—it lets you drill down through all the relationships (see Figure 5-10).

Figure 5-10. There are two relationships at work here. Customers is the parent of Orders (which lists all the orders a customer’s placed). Orders is the parent of OrderDetails (which lists the individual items in each order). By digging through the levels, you can see what each customer bought.

So far, you’ve seen how relationships make it easier to review and edit your records. But what about when you add your records in the first place? Relationships are usually based on an unhelpful AutoNumber value. When you create a new doll, you probably won’t know that 3408 stands for Bobelle House O’ Dolls. Access stops you from entering a manufacturer ID that isn’t linked to anyone at all, but it doesn’t help you choose the ID value you want.

Fortunately, Access has a technique to help you out. In the previous chapter, you learned about lookups (Section 4.4.1), a feature that provides you with a list of possible values for a column. When creating a lookup, you can supply a list of fixed values, or you can pull values from another table. You could create a lookup for the ManufacturerID field in the Dolls table that uses a list of ID values drawn from the Manufacturers table. This type of lookup helps a bit—it gives you a list of all the possible values you can use—but it still doesn’t solve the central problem. Namely, the befuddled people using your database won’t have a clue what ID belongs to what manufacturer. You still need a way to show the manufacturer name in the lookup list.

Happily, lookup lists provide just this feature. The trick’s to create a lookup that has more than one column. One column holds the information (in this case, the manufacturer name) that you want to display to the person using the database. The other column has the data you want to use when a value’s picked (in this case, the manufacturer ID).

The following steps show how you can create a lookup list that links the Dolls and Manufactures tables:

Now, if you switch to the datasheet view of the Dolls table, you can use your lookup when you’re editing or adding records (Figure 5-14).

Figure 5-14. Even though the Dolls table stores an ID value in the ManufacturerID field behind the scenes, that’s not how it appears on your datasheet. Instead, you see the related manufacturer name (both onscreen and in any printouts you make). Even better, if you need to add a new record or change the manufacturer that’s assigned to an existing one, then you can pick the manufacturer from the list by name.

A one-to-one relationship links one record in a table to zero or one record in another table. People sometimes use one-to-one relationships to break down a table with lots of fields into two (or more) smaller tables.

A Products table may include detailed information that describes the product and its price, and additional information that describes how it’s built. This information’s important only to the people in the engineering department, so you may choose to split it into a separate table (named something like Products-Engineering). That way, sales folks don’t need to think about it when they’re making an order. Other times, you might break a table into two pieces because it’s simply too big. (Access doesn’t let any table have more than 255 fields.)

You create a one-to-one relationship in the same way you create a one-to-many relationship—by dragging the fields in the Relationships tab (Figure 5-15). The only difference is that the linked fields in both tables need to be set to prevent duplicates. This way, a record in one table can (at most) be linked to a single record in the other table.

Figure 5-15. When you link two fields that don’t allow duplicates (and you have the Enable Referential Integrity option switched on), Access realizes that you’re creating a one-to-one relationship. Access places the number 1 at each side of the line to distinguish it from other types of relationships. In this example, the ID column in the Products table and the ID column in the ProductsEngineering table are both primary keys of their respective tables, so there’s no way to link more than one record in ProductsEngineering to the same record in Products.

A many-to-many relationship links one or more records in one table to one or more records in another table. Consider a database that tracks authors and books in separate tables. Best-selling authors don’t stop at one book (so you need to be able to link one author to several books). However, authors sometimes team up on a single title (so you need to be able to link one book to several authors). A similar situation occurs if you need to put students into classes, employees into committees, or ingredients into recipes. You can even imagine a situation where this affects the bobblehead database, if more than one manufacturer can collaborate to create a single bobblehead doll.

Many-to-many relationships are relatively common, and Access gives you two ways to deal with them.

Junction tables

Junction tables are the traditional approach for dealing with many-to-many relationships, and people use them throughout the database world (including in industrial-strength products like Microsoft SQL Server). The basic idea’s that you create an extra table that has the sole responsibility of linking together two tables.

Each record in the junction table represents a link that binds together a record from each table in the relationship. In the books and authors database, a single record in the junction table links together one author with one book. If the same author writes three books, then you need to add three records to the junction table. If two authors work on one book, then you need an additional record to link each new author.

Suppose you have these records in your Authors table:

Table 5-6. 

ID	FirstName	LastName
10	Alf	Abet
11	Cody	Pendant
12	Moe	DeLawn

And you have these records in your Books table:

Table 5-7. 

ID	Title	Published
402	Fun with Letters	January 1, 2007
403	How to Save Money by Living with Your Parents	February 24, 2008
404	Unleash Your Guilt	May 5, 2007

Here’s the Authors_Books table that binds it all together:

Table 5-8. 

ID	AuthorID	BookID
1	10	402
2	11	403
3	12	403
4	11	404

Authors_Books is a junction table that defines four links. The first record indicates that author #10 (Alf Abet) wrote book #402 (Fun with Letters). As you traverse the rest of the table, you’ll discover that Cody Pendant contributed to two books, and two authors worked on the same book (How to Save Money by Living with Your Parents).

Tip

The junction table often has a name that’s composed of the two tables it’s linking, like Authors_ Books.

The neat thing about a junction table is that it’s actually built out of two one-to-many relationships that you define in Access. In other words, the junction table’s a child table that has two parents. The Authors table has a one-to-many relationship with the Authors_Books table, where Authors is the parent. The Books table also has a one-to-many relationship with Authors_Books, where Books is the parent. You can define these two relationships in the Relationships tab to make sure referential integrity rules the day (Figure 5-16).

Figure 5-16. The many-to-many relationship between Authors and Books is really two one-to-many relationships that involve the Authors_Books table. Once you’ve defined these relationships, you can’t link to an author or book that doesn’t exist, and can’t delete an author or book that has a record in the Authors_ Books table.

Although junction tables seem a little bizarre at first glance, most database fans find that they quickly become very familiar. As with the one-to-many relationships you used earlier, you can create lookups (Section 5.2.5) for the AuthorID and BookID fields in the Authors_Books table. However, you’ll always need to add the Authors_Books record by hand to link an author to a book.

Multi-value fields

Up until Access 2007, junction tables were the only option for many-to-many relationships. But to support the SharePoint integration features (Chapter 21), Access 2007 adds a new feature: multi-value fields.

As its name suggests, a multi-value field can store more than one value. This capacity neatly solves the problem of many-to-many relationships. The trick’s to configure the linked field in the child table as a multi-value field. Reconsider the authors and books example. Without the junction table, you’d need to add an AuthorID column to the books table to indicate which author wrote a given Book:

Table 5-9. 

ID	Title	Published	AuthorID
402	Fun with Letters	January 1, 2006	10
403	How to Save Money by Living with Your Parents	February 24, 2005	11
404	Unleash Your Guilt	May 5, 2006	11

But an ordinary field holds a single value. Thus, this table can indicate only one of the two authors for book #403.

However, if you change AuthorID to allow multiple values, you can enter a list of authors, like this:

Table 5-10. 

ID	Title	Published	AuthorID
403	How to Save Money by Living with Your Parents	February 24, 2005	11, 12

Behind the scenes, a multi-value field actually uses a junction table. However, Access hides that detail from you, which makes it a bit easier to link related records.

In order to create a multi-value field, you need to use a lookup. As you’ve already seen (Section 5.3), you can choose to turn on this option in the last step of the Lookup wizard. Alternatively, if you already have a lookup in a field, you just need to make one minor modification. Open the table in Design view, choose the field that has the lookup (like ManufacturerID), and then, in the Field Properties section, click the Lookup tab. Look for the Allow Multiple Values property, and change it from No to Yes.

Note

Once you change your field to support multiple values, you can’t switch back.

Figure 5-17 shows a multi-value lookup list in action.

Figure 5-17. This lookup list uses checkboxes, because it’s on a multi-value field. You can select several values for a single record by checking more than one item. So you can indicate that a single doll was created by a two-manufacturer partnership.

Multi-value fields are available only if you’re using the new .accdb database format (Section 1.2.2). You can’t use them with an .mdb file (a database that was created in Access 2003 and hasn’t been converted yet).

Multi-value fields also cause headaches if you want to upsize your database to SQL Server (as described in Chapter 20), because SQL Server doesn’t support them. So if there’s a possibility that you’ll need to share your database with lots of people (say, in a large company), and you might move your data to a high-powered SQL Server database someday, avoid multi-value fields.

Note

Multi-value fields don’t pose a problem if you want to upsize your database to SharePoint Server (as described in Chapter 21).

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.

Identifying the relationships

It’s fairly easy to pick out the relationships you need. Students take classes. Teachers teach classes. This suggests two relationships—one 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.

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 relationships—but 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 for—namely, 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.

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.

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.

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 sense—for 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.

Ordering products

It doesn’t matter how fancy your sales database is—if 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 data—in 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.