Weak, Associative, and Reference Entities

Learning outcomes

Not every entity type has completely independent identity. Some depend on a parent, some exist to represent a relationship that has its own facts, and some provide controlled reusable values shared across the model. These patterns are commonly described as weak entities, associative entities, and reference entities.

Weak entities: identity depends on an owner

A weak entity cannot be uniquely identified by its own attributes alone in the chosen model. Its identity includes the identity of an owning entity plus a partial key.

A classic example is a line number within a work order:

WorkOrderLine  work_order_id  -- owner identity  line_number    -- partial key  description  quantityPRIMARY KEY (work_order_id, line_number)

Line 3 is not globally meaningful. It means “line 3 of work order 1842.” The owner provides part of the identity.

Weakness is about identification, not importance

“Weak” does not mean unimportant, optional, or low quality. A weak entity may contain critical business facts. The term describes identification dependency.

Common examples can include:

• order line identified by order + line number;
• document page identified by document + page number;
• room identified by building + room number;
• dependent identified within an employee record by an organization-defined local name/number.

Weak entity or surrogate-key child?

A relational implementation may still give a weak conceptual entity a surrogate key:

work_order_line_id INTEGER PRIMARY KEY,work_order_id      INTEGER NOT NULL,line_number        INTEGER NOT NULL,UNIQUE (work_order_id, line_number)

The surrogate does not erase the business identification rule. If users know a line as “line 3 within work order 1842,” the composite uniqueness should remain enforced.

Associative entities: relationships with facts

A many-to-many relationship frequently becomes an associative entity. The entity represents the association itself and stores facts about that association.

WorkshopHub's WorkOrderAssignment is a strong example:

WorkOrderAssignment(  work_order_id,  technician_id,  started_at,  ended_at,  role)

The important facts—start time, end time, and role—belong neither to WorkOrder alone nor Technician alone. They describe a technician's participation in a particular work order.

A join table can be a real domain concept

Calling associative entities “just join tables” can hide business meaning. Enrollment, Membership, Assignment, Reservation, Subscription, PartUsage, and ShipmentItem frequently have their own lifecycle and constraints.

Associative entity identity

There are several reasonable key strategies depending on semantics.

Composite key from parents

PRIMARY KEY (student_id, course_id)

This works if a student can enroll in a course at most once for all time.

Composite key including time/attempt

PRIMARY KEY (student_id, course_id, attempt_number)

This works if repeated enrollments are meaningful and attempt number is governed.

Surrogate association ID plus business uniqueness

enrollment_id INTEGER PRIMARY KEY,UNIQUE (student_id, course_id, term_id)

This is useful when many other entities need to reference the association itself.

Reference entities: governed reusable values

A reference entity contains a controlled set of reusable values such as currency, country, work-order type, failure category, or technician role. It can centralize descriptions and metadata.

WorkOrderStatus(  status_code,  display_name,  is_terminal,  sort_order)

Then WorkOrder references status_code. This can be useful if statuses carry metadata or are administered independently.

Do not create a reference table for every enum

A lookup/reference table adds joins, migration requirements, and another object to govern. If a tiny set is fixed by application semantics, a check constraint may be simpler:

status TEXT NOT NULLCHECK (status IN ('open','scheduled','in_progress','closed','cancelled'))

A reference entity is more justified when values:

• have descriptions or additional metadata;
• are shared by many tables;
• need localization;
• change independently through administration;
• have effective dates or lifecycle state;
• come from an external standard.

Reference data versus master data

The terms vary between organizations, but a useful distinction is:

• Reference data classifies other data using relatively small controlled sets: country codes, currencies, status codes.
• Master data represents important business entities shared across processes: customer, product, supplier, employee.

Both may be centrally governed, but their modeling roles differ.

WorkshopHub: three patterns together

SQL example: association and reference data

CREATE TABLE technician_role (    role_code    TEXT PRIMARY KEY,    display_name TEXT NOT NULL UNIQUE);CREATE TABLE work_order_assignment (    assignment_id INTEGER PRIMARY KEY,    work_order_id INTEGER NOT NULL,    technician_id INTEGER NOT NULL,    role_code     TEXT NOT NULL,    started_at    TEXT NOT NULL,    ended_at      TEXT,    FOREIGN KEY (work_order_id) REFERENCES work_order(work_order_id),    FOREIGN KEY (technician_id) REFERENCES technician(technician_id),    FOREIGN KEY (role_code) REFERENCES technician_role(role_code),    CHECK (ended_at IS NULL OR ended_at >= started_at));

The example uses a surrogate assignment key because future time logs, notes, or audit records may need to reference a particular assignment. A natural uniqueness rule may still be required to prevent duplicate or overlapping assignments.

Model lifecycle explicitly

Special entity types often reveal lifecycle rules that a simple many-to-many diagram hides:

• Can an assignment overlap another assignment for the same technician?
• Can a part-usage record be edited after invoicing?
• Can a reference code be deleted after historical rows use it?
• Can a weak line number be reused after deletion?

These questions influence constraints, transactions, and audit design in later chapters.

Chapter 2 checkpoint

Summary and next chapter

Chapter 2 established the internal structure of entity modeling. You can now discover entities, define attributes and domains, reason about candidate keys, choose natural and surrogate key strategies, and recognize weak, associative, and reference entities. Chapter 3 builds on this foundation by modeling relationships precisely: cardinality, participation, recursive relationships, and many-to-many resolution.

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.
• C. J. Date, Database Design and Relational Theory.