Relationships Between Entities

Relationships are the final component in an ER Model, allowing for a logical linkage between one entity and another. A relationship in an ER Model connects the data elements of two entities that contain information about the same element. The primary entity in a relationship provides some of the data, and other entities provide further related data. A relationship definition states how two entities are connected.

In the modeling process, we attempt to discover which things are related to one another, and how they are related, within the business problem we are modeling. They are usually defined as a link connecting the entities together based on the number of data elements in one entity that are related to one or more elements in the other entity. This is known as the cardinality of a relationship.

The cardinality of a relationship is used to define how many elements in one entity match up with elements of another entity. Relationships are usually defined as a numeric link connecting the entities together based on the number of data elements in one entity that are related to one or more full elements in another entity. It can be described as how many of one thing can relate to how many of something else.

Relationships cause a situation known in data modeling as a dependency. A dependency is a circumstance in which one entity either can't exist or has little meaning without at least one other entity in the database. When a dependency exists, it becomes a table relationship in the physical database design. There are three basic types of entity dependencies, and these dependencies are based on element cardinality. They are discussed in the points that follow:

• One-to-one dependency A one-to-one dependency is the rarest form, because each record in one entity correlates to exactly one record in the other.

• One-to-many dependency A one-to-many dependency is the most common form of relationship. One record in a primary entity has ties to many records in a secondary entity.

• Many-to-many dependency A many-to-many dependency exists when many records in one entity can relate to many records in another entity.

Relationships are implemented as parent and child entities. In all cases in the ER Model, a key attribute from a child entity is attached to a related key value in a parent. All cardinality of relationships is implemented in this way, meaning that whether you have a one-to-one, one-to-many, or many-to-many relationship, you always maintain integrity by having the key of the child related to a parent.

Identification of Relationships

Setting up the relationships will finalize a draft of the ER Model. This draft will undergo modifications as the database approaches a physical design. Because at this stage of design all the basic elements of data model have been completed using the Entity Relationship approach, we now have a working model allowing us to proceed further into development. A general listing of attributes for each entity and the relationships between these entities is an important springboard to use to progress through the database design to the eventual completed system.

Implementing the relationships and applying normalization principles are often performed as parallel processes because decisions made in one process effect the other process and vice versa. Normalization helps in determining cardinality and the cardinality is a requirement of each relation. The three basic cardinality types, one-to-one, one-to-many, and many-to-many, are partially a result of knowing the business scenario being modeled and partially derived from applying normal forms.

One-To-One Relationship

The one-to-one type of relationship occurs when one row or data element of an entity is associated with only one row or element in the second entity. This type is used mostly when an entity has an extraordinarily large number of attributes so the entity is split in two to make it easier to manage. Also, an extra entity might be desired when developing the physical storage locations of the data. By separating seldom-used data from more frequently used information, you can accommodate faster data retrieval and updates. It is for this reason that these types of relationships are pulled into the model until the physical design of the database has begun.

In modeling a one-to-one relationship, a common key must be present in each of the entities being related. This common key allows for the collective attributes of both entities to be retrieved using a single value. Consider, for example, a product that has many descriptive attributes. Two product entities could be used to separate the different properties. Each entity would use a product number or similar value as a key. This is illustrated in the model segment shown in Figure 2.4.

Figure 2.4. Two product entities in a one-to-one relationship.

In the preceding example the ProductCommon entity is used to store the attributes that are most readily used, and the ProductAtypical entity contains other attributes that, though still needed, are less frequently used. It is much more common to find relationships existing in a one-to-many cardinality.

One-To-Many Relationship

One-to-many relationships exist when a single instance of an entity (the parent entity) relates to many instances of another entity (the child entity). One-to-many dependencies are a natural occurrence in the real worldfor example, a customer will have many orders, and a manufactured product could have many components.

One-to-many relationships can be expressed as many-to-one as well, though one-to-many is a common standard. It depends on how the relationship is being viewed.

This relationship is a classic parent-child dependency. A foreign key in a child entity will point to the associated primary key of the parent. When this relationship is related, removal of a parent could cause orphaning of the child because of its dependency. There are many examples of this type of relationship in the Northwind order process. The following list represents those seen in the diagram to this point:

• One supplier to many products

• One order to many order details

• One product exists within many order details

• One employee has many orders

• One customer has many orders

• One shipper is used in many orders

• One category will contain many products

Although one-to-many usually establishes "many" as the normal numerical component, you can have zero or only one child row. Customers can have zero or one order as well. In fact, cardinality notation allows for this with 0..*. As we complete more of the information in the business scenario for Northwind, we will see more of these dependencies occur. One-to-many relationships are also a facet of implementing many-to-many relationships, as discussed in the next section.

Many-to-Many Relationship

A modeling differentiation is made in preparing the many-to-many type of relationship. Many-to-many relationships exist when many elements of one entity are related to many elements of another. For this reason, many-to-many relationships are implemented a bit differently in a database environment. In itself, this relationship is not solely one entity to another. In the ER model and database design, a third, joining entity is used to complete two one-to-many relations.

This type of relationship is not uncommon in the real world. As stated, a many-to-many relationship is implemented using three entities. The two main entities are connected together using a third entity. The third entity contains keys connected to the other two entities. In our basic data model the Order Detail entity is just such an entity. In the Northwind example this entity, however, also exists in its own right and contains additional attributes of its own.

In many models the third entity is created for the purpose of joining two other entities and has no other attributes except for those needed as key values to connect to the original entities. Consider, if you will, an educational scenario in which a teacher instructs several different bodies of students and a student has several different teachers. A third entity, TeacherStudent, could be created to connect the main entities.

This new entity that is created is known as an associate entity or a join entity. Resolving many-to-many relationships involves creating two one-to-many relationships from each of the original entities onto the associative entity. Take the many-to-many relationship between student and teacher. A student can have many teachers while a teacher has many students. A many-to-many relationship will need to be resolved by creating an associative entity and then linking a one-to-many relationship from the Teacher and Student entities to the TeacherStudent entity, as shown in Figure 2.5.

Figure 2.5. A teacher/student many-to-many relationship.

Along with these very standard relationships, you will find in some occurrences that a relationship is made within a single entity. In the case of a relationship in which an entity is related to itself, you have a unique situation, which in modeling terms is called a unary or a self-referencing entity. In a physical implementation this relationship is implemented through a self-join.

An example of this type of relationship is present within the Northwind system. Within the Employee entity, the ReportsTo attribute will hold the value of an Employee Identifier. This identifier refers to another employee element that is the boss or some other responsible person. The ReportsTo element will be a foreign key that refers to the primary key of the Employee entity. The modeling of this type of relationship is shown in Figure 2.6.

Figure 2.6. A unary relationship.

A unary relationship will constitute a very small percentage of the relationships defined in any given model. These relationships can be defined using a one-to-one or one-to-many cardinality. In an employee scenario a manager can have a single assistant (one-to-one) or any number of employees can report to the same manager (one-to-many).

Relationships will continue to be added to the model as elements in the system evolve. A potential model for the Northwind Trader order process can now be assembled and is provided in Figure 2.7.

Figure 2.7. The completed Northwind model.