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.

The choice of primary and foreign relations isn't arbitrary. It is determined first by the cardinality of the relationship and secondin the rare situation where there is any doubtby 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.

On Diagramming Relationships You'll notice some differences between Figure 3-3 and the formal E/R diagram shown in Figure 1-6 on page 22. 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 separately to be distracting, and it clutters up the diagram. Second, the relationships are not labeled. Some analysts (and some diagramming tools) add a descriptive label to each relationship. In our example, the label might be something along the lines of "Orders consist of OrderDetails". 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. 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 Microsoft Visio. But Microsoft Access, Microsoft SQL Server, and Microsoft Visual Studio all provide diagramming tools that have their own conventions, 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 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.

And, 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, OrderIDthe candidate key of the Orders relationhas been added to the OrderDetails relation. Orders is the primary relation, and OrderDetails is the foreign relation.

Note that 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.

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

Separate relations representing relationships complicate the data model somewhat, and one might be tempted to simply include the relationship attributes in one of the participating relations. This approach isn't necessarily disastrous, but if there are a lot of attributes or a lot of relations with attributes, it 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, for example, is a State Transition diagram describing the possible legal changes in an individual's marital status.

State Transition diagrams are not difficult to understand. Each vertical line indicates a valid state, while a horizontal line represents a change from one state to another. 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 status, 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.