July 28, 2011, 11:59 a.m.
posted by suppafly
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.
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.
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:
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.
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 shows a snapshot of this arrangement.
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 without 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).
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 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 shows the whole she-bang (not including the ClassDescriptions table shown in Figure).
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 TeacherPayments 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.
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.
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:
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. BoutiqueFudge 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 shows this design.
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.
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). 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) and the forms feature (Chapter 9).
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 databasedriven 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.