Understanding Relationships

We've dealt with the first two steps of the design process now: the sorting out of entities and attributes. After you have what you think is a decent draft of a set of entities and attributes, the next thing to do is to start to consider how these entities relate to one another. You need to become familiar with the fundamental types of entity relationships, and also with a simple notation for representing relationships graphically in a diagram.

Representing Relationships in a Diagram

Consider a system that stores information about farmers and pigs, among other things. Farmers and pigs are each entities, and these two entities have a direct relationship, in that each pig ties back to a single farmer.

There's a name for the farmer-pig relationship. It's called a one-to-many relationship, meaning that for each farmer there may be any number of pigs. "One farmer," as we usually put it, "can sell many pigs."

Now you can expand on the entity-relationship notation. You already have a graphical shorthand for depicting the entities and attributes in a database system. Next you should add some conventions for showing the relationships among them. Each entity can be represented by a box, as before, and each relationship can be represented by a line that indicates the relationship type. In this simple notation, you'd depict the relationship between farmers and pigs along the lines of Figure 5.4.

Figure 5.4. Entity-relationship notation for a database that stores information about farmers and pigs.

Notice the line between the two entities that depicts their relationship branches out where it touches the Pig entity. In a one-to-many relationship, this fork or branch indicates the "many" end of the relationship. So this notation tells us that one farmer may be linked to many pigs. If the fork were on the other end, this would imply that one pig could be associated with many farmers, which would be a very different assertion about the data we're trying to model.

Relationship Types

Those simple graphical conventions are the foundation of what you need to draw your entity-relationship diagrams (ERDs). Another important concept is an understanding of the different relationship types that you could encounter. You need to reckon with four types: the one-to-many and many-to-one relationships (the latter is simply a one-to-many relationship looked at from the other direction); the one-to-one relationship, which is a rare case you probably won't encounter much; and the many-to-many relationship, a common but more complicated relationship to which we'll need to devote special attention.

We'll consider each of these relationship types in turn , and show how to represent them in the ERD notation.

One-to-One Relationships

This relationship type is rather rare in practice. For example, consider a dataset concerning children and their birth records. Let's say that for now, you've decided that children and birth records should represent separate entities.

In a standard analysis sequence, after you've decided on entities and attributes, you'll start to ask questions about relationships. What's the relationship between children and birth records? Can one child have many birth records? No, each child is born only once. And can one birth record pertain to more than one child? Again, probably not. So the relationship between a child and a birth record appears to be one-to-one. You can depict that as shown in Figure 5.5.

Figure 5.5. This ERD shows the one-to-one relationship between children and birth records. A single line with no " crows -feet" is used.

In general, as we said, it's rare to let the two sides of a one-to-one relationship stand as separate entities. In general, you'll fold one of the entities into the other. In this case, you might decide to move all the attributes of a birth record into the Child entity and get rid of Birth Record as a separate entity.

When Is One to One the Right Choice?

There are some circumstances that would justify keeping two separate entities, even when the relationship between them is one to one. Probably the clearest case occurs when one of the entities represents data that's filled out only in infrequent cases. Such could be the case in a database that stores information about spacecraft. For simplicity, assume that all the relevant information on a spacecraft can be represented by a single entity, called Craft.

Now further suppose that when a spacecraft reaches the end of its useful life, it's formally decommissioned, and at that point a huge amount of data is gathered ”once and only once ”as part of the decommissioning process. For this example, assume there are an additional three hundred attributes you need to track when a craft is decommissioned. You could add all those attributes to the Craft entity. But in actual use, those columns are almost always going to be empty. They won't be filled until a craft is taken out of service. This leads to the potential for large "holes" in the actual, physical database. In other words, at the implementation level, it could be very wasteful to have those three hundred data slots ready and waiting when they're used very infrequently.

One solution here would be to have Craft and Decommission as two separate entities in a one-to-one relationship. You would create a Decommission entry for a Craft only when you actually needed it, and your view of the data would be a little cleaner. For example, to find all ships that had been decommissioned, you'd just run a search in the Decommission table. If the system contained only a Craft entity, you might end up needing some special additional attribute to signify that a ship had been decommissioned, or else you'd have to rely on certain specific attributes, such as "decommission date" being empty if the ship hadn't been decommissioned yet.

One-to-Many Relationships

We've already devoted some attention to the one-to-many relationship. The relationship of a customer to sales, of a farmer to pigs, and of a passenger to trips are all examples of one-to-many relationships. And you've seen the "crow's- foot " notation for indicating these relationships, where the fork notation indicates the "many" side of the relationship.

There's another piece of terminology for one-to-many relationships that's helpful to know. You'll frequently see the entity that represents the "one" side of the relationship referred to as the parent entity, whereas the "many" side is often referred to as the child entity. If you hear a database architect blurt out a reference to a "child" table, odds are she's referring to the entity on the "many" side of a one-to-many relationship.

Many-to-One Relationships

There's no difference at all between the concepts of a one-to-many and a many-to-one relationship. They're the same idea, just seen from different points of view. If the relationship between customers and sales is one to many, then it's equally true that the relationship between sales and customers is many to one. Customer is the parent of Sale, Sale is the child of Customer. These statements are equivalent. Figure 5.6 shows the Customer-Sale relationship. Whether you choose to describe this as a one-to-many or a many-to-one depends on which "side" you start from in your description. The relationship of a customer to a sale is one to many; the relationship of a sale to a customer is many to one. One-to-many and many-to-one are two sides of the same coin; a relationship can't be one without being the other.

Figure 5.6. The Customer-Sale relationship drawn as both a one-to-many and a many-to-one relationship.

Many-to-Many Relationships

Consider the relationship between actors and movies. One actor may play roles in many movies. One movie involves roles played by many actors. Each actor can relate to many movies. Each movie may be associated with many actors. This is a classic many-to-many relationship. You can depict it as shown in Figure 5.7.

Figure 5.7. Entity-relationship notation for a many-to-many relationship.

Many-to-many relationships are extremely common in relational database systems. Here are examples of some other many-to-many relationships.

• Attorney ”Case ” One attorney may serve on many cases, and one case may involve many attorneys .

• Player ”Game ” One player may play in many games , and one game involves many players.

• Product ”Invoice ” One invoice may contain orders for many products, and one product may be ordered on many different invoices.

• Student ”Class ” One student may participate in many classes, and one class may have many students enrolled.

You can probably think of your own examples pretty easily as well.

Many-to-many relationships are a bit trickier than the others to actually implement in real life. When we get to the details of how to build a FileMaker database based on an ERD, you'll see the specific techniques you need to bring a many-to-many relationship to life in FileMaker. For now, though, we'll just use the ERD as an analysis tool, and not worry about implementation.

Relationship Cardinality

You've seen how to filter a process description into a list of entities and their attributes, and you've seen a useful language for describing the relationships between those entities. So far, in describing these relationships, we've been mainly concerned with the question "how many?" How many purchases can relate to a customer? One, or many? And how many customers can participate in a purchase?

The answers to these questions tell you into which of the three (or four) relationship types a given relationship falls . This information is sometimes referred to as the cardinality of the relationship. Cardinality specifies whether a relationship is one to one, one to many, many to one, or many to many.