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 considering 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 have 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 what's shown in Figure 5.4.
Figure 5.4. Entity-relationship notation for a database that stores information about farmers and pigs.
Notice that 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 in order to draw your entity-relationship diagrams. Another important concept is an understanding of the different relationship types you could encounter. You need to reckon with four types: the one-to-one relationship, which is a rare case you probably won't encounter much; the one-to-many and many-to-one relationships (the latter is simply a one-to-many relationship looked at from the other direction); 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
The one-to-one 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 "crow's-foot" is used.
It's rare to let the two sides of a one-to-one relationship stand as separate entities. Instead, you'll often 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.
One-to-Many Relationships
We've already devoted some attention to the one-to-many relationship. The relationships of a customer to sales, of a farmer to pigs, and of a worker to timesheets are all examples of one-to-many relationships. And you've seen the crow's-foot notation for indicating these relationships, in which 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, and one movie involves roles played by many actors. So each actor can relate to many movies, and 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.
Relationship Optionality
|