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 5-18).
Figure 5-18. 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 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-19 shows the whole she-bang (not including the ClassDescriptions table shown in Figure 5-18).
Figure 5-19. 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 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.
Print command disabled when I’m looking at the Relationships tab?Popularity: 1% [?]
Recent Comments