When we draw lines within an entity-relationship diagram (ERD) that represent a “relationship,” the diagram only shows the exterior presentation. A relates to B in a one-to-many fashion. But what is the actual nature of that A-to-B relationship? Is the relationship one of composition where A is the whole and each B is a part of A? Can any B exist without an A? Consider an order and its possibly many related order items. If the order is deleted, then the order items also must be deleted. One exists within “the bubble” of the other. Or maybe, instead of a composition relationship, the connection is much looser, just a reference relationship.
Each A and B, or perhaps we can call them parent and child, may exist independently. Maybe we have a circumstance where there is a customer and an order. A customer may have zero-to-many orders, and an order has zero-or-one customer(s). Orders reference customers, but customers can exist without orders, and orders can exist without customers. The referenced entity is not dependent on the existence of the referencing entity.
There are more complex kinds of relationships. A relationship line coming from an entity and circling back into the same entity is a recursive relationship. Recursive relationships occur more often than one might imagine. An easy example is a bill-of-materials, where a list of all materials relates back into itself, as finished goods are made from raw materials within the same full listing. Or, in another example, one employee can serve as the manager of another employee. One-to-many, a single employee can manage zero-to-many employees.
Generalization is another often-seen relationship. Often called a “super type/sub-type” relationship, as the high-level abstracted version relates to possibly many more specific versions of the initial abstraction. This relationship has many names. It can be referred to as abstraction, or even an “is a” relationship. Here is an example: “a vehicle is a car” or “a vehicle is a truck.”
Additionally, there is another relationship that is the opposite of generalization, and that is referred to as an exclusive. With an exclusive relationship, a child may have more than one parent. Think of a file. The file may exist in a folder, or on a root drive. The file “child” may have a drive or a folder as its immediate “parent.”
When two specific entities share more than one relationship, regardless of type, these relationships need to identify the role they are playing to distinguish themselves. Why do these relationships exist? In this circumstance, the foreign key columns will need to have differing names, where, ideally, the names flag the role they are involved in. Whether just two relationships or a dozen, each one must be identified with care so that everyone knows the distinctions between each and can use them properly.
Far too often, people do not appreciate what they know. Since people know whatever they know, they assume that everyone must also know this same intention, or believe it is obvious to even the most casual observer. Sadly, these folks are often mistaken.
Many people do not know the information in question. This is true, even in this instance, when it may seem obvious what a relationship between two tables or entities actually means. Everyone has a different perspective and is often aware of differing aspects across situations.
It is because of this wild viewpoint variance that it is so important for everyone to document those obvious items, be it in table or column or foreign key definition, or somewhere within a proper data catalog. The obvious is not necessarily so obvious. Knowledge is important, and sharing that knowledge is vital.