Relationship Semantics and Role Names

Learning outcomes

Entities do not exist in isolation. Most useful data comes from the connections between them: a customer owns an asset, an asset has work orders, a technician is assigned to a work order, and a part is consumed by a repair. A relationship is therefore not merely a line between boxes. It is a statement about the domain.

A relationship is a fact about entities

A relationship type describes how one or more entity types can be associated. A relationship instance is one specific association among entity instances.

Notice that the relationship has semantic content. The same pair of entity types may support several different relationships. An Employee can open a work order, approve a work order, or perform work on a work order. Those verbs are not interchangeable.

Relationship names should read like sentences

A useful ER relationship name allows the diagram to be read in both directions. For example:

Names such as link, relates_to, mapping, or has_data usually communicate too little. Prefer verbs from the domain language.

Relationship degree

The degree of a relationship is the number of participating entity types.

• Unary/recursive — one entity type participates in different roles, such as Employee supervises Employee.
• Binary — two entity types participate, such as Customer owns Asset.
• Ternary — three entity types jointly participate, such as Supplier supplies Part to Warehouse under one agreement.
• n-ary — four or more entity types participate in one relationship.

Binary relationships are the most common, but forcing every real-world fact into binary form can lose meaning. Lesson 4 examines this in detail.

Role names explain participation

A role name describes how an entity type participates. Roles become essential when the same entity type appears more than once.

Suppose WorkshopHub allows one technician to mentor another:

Technician -- mentors -- Technician    mentor role           mentee role

Without the role names mentor and mentee, the two ends are ambiguous. The database implementation might use:

technician_mentorship(  mentor_technician_id  -> technician,  mentee_technician_id  -> technician,  started_at)

One entity type can have multiple relationships to another

Consider Employee and WorkOrder. A work order may have:

• an employee who opened it;
• an employee who approved a quote;
• one or more technicians assigned to perform work;
• an employee who closed it.

These facts should not be merged into one vague Employee–WorkOrder relationship. The relationships have different semantics, cardinalities, timing, and rules.

Relationship or copied attribute?

A common anti-pattern stores descriptive data instead of a relationship:

WorkOrder(  work_order_id,  customer_name,  technician_name,  part_sku)

These text values do not connect the work order reliably to Customer, Technician, and Part entities. If the name changes, the copied text drifts. If two technicians share a name, identity is ambiguous.

A relationship is normally implemented with keys:

WorkOrder.asset_id -> Asset.asset_idWorkOrderAssignment.technician_id -> Technician.technician_idPartUsage.part_id -> Part.part_id

Relationship attributes

Sometimes a fact belongs to the relationship rather than either participant. For Technician assigned to WorkOrder, these values describe the association:

• started_at;
• ended_at;
• assignment_role;
• allocation_percent.

Placing started_at on Technician would be wrong because a technician has many assignments. Placing it on WorkOrder would also be wrong if several technicians participate. The fact belongs to this technician's assignment to this order.

Relationship semantics before foreign keys

A foreign key implements a relationship in a relational schema, but the model should define the relationship first. For example:

Asset.customer_id REFERENCES Customer(customer_id)

does not by itself explain:

• whether asset ownership is current-only or historical;
• whether an asset can have more than one owner;
• whether ownership is mandatory;
• whether customer changes represent transfers;
• whether a customer may own zero assets.

Those are relationship semantics and constraints. The foreign key is one implementation mechanism.

Read the WorkshopHub model as statements

A useful conceptual review sounds like this:

1. A Customer may own Assets.
2. Every Asset is currently owned by one Customer.
3. An Asset may have WorkOrders.
4. Every WorkOrder services one Asset.
5. A Technician may receive many WorkOrderAssignments.
6. Every WorkOrderAssignment refers to one Technician and one WorkOrder.
7. A WorkOrder may consume Parts through PartUsage records.

Each sentence can be challenged by a stakeholder. This is exactly what a good conceptual model should enable.

Practice: improve the relationship vocabulary

Summary and next lesson

Relationships are domain facts expressed between entity instances. Good relationship names make those facts readable, role names remove ambiguity, and relationship attributes reveal when an association deserves first-class treatment. The next lesson adds the most visible relationship constraint: cardinality.

References

• Peter P. Chen, “The Entity-Relationship Model—Toward a Unified View of Data,” 1976.
• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• Thomas Connolly and Carolyn Begg, Database Systems.