Resolving Your Relationships
The aim of a good data model is to create a structure that provides the database server with quick access. To further refine the telephone-directory data model, you can resolve the relationships and normalize the data model. This section addresses the hows and whys of resolving your relationships. Normalizing your data model is discussed in "Normalizing Your Data Model".
Resolving m:n Relationships
Many-to-many (m:n) relationships add complexity and confusion to your model and to the application development process. The key to resolving m:n relationships is to separate the two entities and create two one-to-many (1:n) relationships between them with a third intersect entity. The intersect entity usually contains attributes from both connecting entities.
To resolve a m:n relationship, analyze your business rules again. Have you accurately diagrammed the relationship? In the telephone-directory example, we have a m:n relationship between the name and fax entities as Figure 8-17 shows. To resolve the relationship between name and fax, we carefully reviewed the business rules. The business rules say: "One person can have zero, one, or many fax numbers; a fax number can be for several people." Based on what we selected earlier as our primary key for the voice entity, a m:n relationship exists.
A problem exists in the fax entity because the telephone number, which is designated as the primary key, can appear more than one time in the fax entity; this violates the qualification of a primary key. Remember, the primary key must be unique.
To resolve this m:n relationship, you can add an intersect entity between name and fax entities. The new intersect entity, faxname, contains two attributes, fax_num and rec_num. The primary key for the entity is a composite of both attributes. Individually, each attribute is a foreign key that references the table from which it came. The relationship between the name and faxname tables is 1:n because one name can be associated with many fax numbers; in the other direction, each faxname combination can be associated with one rec_num. The relationship between the fax and faxname tables is 1:n because each number can be associated with many faxname combinations.
Resolving Other Special Relationships
You might encounter other special relationships that can hamper a smooth-running database. The following list shows these relationships:
A complex relationship is an association among three or more entities. All the entities must be present for the relationship to exist. To reduce this complexity, reclassify all complex relationships as an entity, related through binary relationships to each of the original entities.
A recursive relationship is an association between occurrences of the same entity type. These types of relationships do not occur often. Examples of recursive relationships are bill-of-materials (parts are composed of subparts) and organizational structures (employee manages other employees). See Chapter 5, "Programming with SQL," for an extended example of a recursive relationship. You might choose not to resolve recursive relationships.
A redundant relationship exists when two or more relationships are used to represent the same concept. Redundant relationships add complexity to the data model and lead a developer to place attributes in the model incorrectly. Redundant relationships might appear as duplicated entries in your entity-relationship diagram. For example, you might have two entities that contain the same attributes. To resolve a redundant relationship, review your data model. Do you have more than one entity that contains the same attributes? You might need to add an entity to the model to resolve the redundancy. The INFORMIX-Universal Server Performance Guide discusses additional topics that are related to redundancy in a data model.
|
