Modeling Relationships

Once you've determined that a relationship exists, you must model it by including attributes from one relation (the primary relation) in the other (the foreign relation), as shown in Figure 3-3.

Figure 3-3. A relationship is modeled by including attributes from the primary relation (Orders) in the foreign relation (OrderDetails).

You'll notice some differences between this diagram and the formal E/R diagram shown in Figure 1-6 (Chapter 1). First, the attributes are not shown as separate objects. At this level of design, you're primarily interested in the relationships between entities, not their composition. I find showing the attributes to be distracting, and it clutters up the diagram.

Second, the relationships are not labeled. I find labeling unnecessary, and because the description of the relationship changes depending on which direction you're reading it (teachers teach students, but students learn from teachers), labels can sometimes be confusing. However, although I don't label the relationships, I do sometimes indicate the attribute that will be used to implement the relation in the database schema. This can be useful, for example, if the primary entity has more than one candidate key and you want to show explicitly which one is to be used.

As I said, I've found this style of diagram useful when working with clients, and it's easy to draw by hand or by using a diagramming tool such as Visio Professional or Micrografx Flowcharter 7. But Microsoft Access, Microsoft SQL Server, and Microsoft Visual Basic all provide diagramming tools as well, and you may decide to use them in preference to, or in addition to, the technique I've shown.

The advantage of using either the Access relationships window (for a Jet database engine .mdb file) or database diagrams (for a database implemented using SQL Server) is that the diagrams become part of the database and automatically reflect changes to it. Unfortunately, this is also the greatest disadvantage of these tools. You can't build abstract diagrams; you must first build the physical tables. There is always a danger in jumping into anything that even looks like implementation too early in the design process, before the implementation model has been finalized.

In my own work, I often use both abstract diagrams and ones that are embedded in the database. I create abstract diagrams in the early stages of the design process, and use one of the Microsoft tools once the conceptual design has been finalized and I'm documenting the physical database schema.

Of course, you don't just copy any attributes from the primary relation to the foreign relation; you must choose attributes that uniquely identify the primary entity. In other words, you add the attributes that make up the candidate key in the primary relation to the foreign relation. Not surprisingly, the duplicated attributes become known as the foreign key in the foreign relations. In the example shown in Figure 3-3, OrderID—the candidate key of the Orders relation—has been added to the OrderDetails relation. Orders is the primary relation, and OrderDetails is the foreign relation.

NOTE

---

The candidate key/foreign key pair that models the relationship need not be the primary key of the primary table; any candidate key will serve. You should use the candidate key that makes the most sense semantically.

The choice of primary and foreign relations isn't arbitrary. It is determined first by the cardinality of the relationship and second—when there is any doubt—by the semantics of the data model. For example, given two relations that have a one-to-many relationship, the relation on the one side is always the primary relation, while the relation on the many side is always the foreign relation. That is, a candidate key from the relation on the one side is added (as a foreign key) to the relation on the many side. We'll be looking at this issue as we examine each type of relationship in the rest of this chapter.

Sometimes you'll want to model not only the fact that a relationship exists, but also certain properties of the relationship—its duration or its commencement date, for example. In this case, it's useful to create an abstract relation representing the relationship, as shown by the Positions relation in Figure 3-4.

Figure 3-4. Abstract relations can model the properties of relationships.

This technique complicates the data model somewhat, and one might be tempted to simply include the relationship attributes in one of the participating relations. However, if there are a lot of attributes or a lot of relations with attributes, this can get unwieldy. More importantly, a distinct relationship entity allows you to track the history of a relationship. The model shown in Figure 3-4, for example, allows you to determine an individual's employment history, which would not have been possible had Position been made an attribute of the Employees relation.

Abstract relationship entities are also useful when you need to track the way a relationship changes over time. Figure 3-5 is an example of a State Transition diagram describing the possible legal changes in an individual's marital status.

Figure 3-5. State Transition diagrams plot the valid changes in an entity's status, in this case in an individual's marital status.

State Transition diagrams are not difficult to understand. Each vertical line indicates a valid change in state. For example, an individual can go from married to divorced and vice versa, but not from divorced to never married. Now, if all you need to model is an individual's current marital state, you don't need to implement an abstract relationship entity to make sure only valid changes are made. But if you ever need to know that John and Mary Smith were married in 1953 and divorced in 1972, and that Mary remarried in 1975 but was widowed in 1986, then you'll need an abstract relationship entity to track that.