Data Modeling
A weak entity is a record whose identity depends on a parent record. In a relational model, you usually represent that dependency with an identifying relationship: the child table’s primary key includes the parent table’s primary key plus a child-side partial key.
When this pattern applies
Use weak entities and identifying relationships when a child record is only distinguishable inside the scope of a parent. The child may have a meaningful local identifier, but that local identifier is not globally unique.
Common examples include an invoice line within an invoice, a room within a building, a chapter within a book edition, or a file within a folder when names are only unique inside the folder. In each case, the local value alone is not enough to identify the record.
The practical question is simple: if someone gives you only the child identifier, can you find exactly one record without knowing the parent? If the answer is no, the child’s identity depends on the parent.
A small dataset with the problem exposed
Consider a facilities model with buildings and rooms. The business uses room numbers such as 101, 102, and 201. Those numbers are unique inside a building, but they repeat across buildings.
If you create a room table keyed only by room_number, the model breaks as soon as two buildings both have room 101. The value 101 is not the identity of a room. It is only a partial key within a specific building.
The durable identity of a room in this model is the pair of values: building_id plus room_number. That pair says which parent building owns the local room number.
| building_id | building_name |
|---|---|
| B01 | North Campus |
| B02 | South Campus |
| building_id | room_number | room_use |
|---|---|---|
| B01 | 101 | Lecture room |
| B01 | 102 | Lab |
| B02 | 101 | Conference room |
| B02 | 201 | Office |
What makes the relationship identifying
An identifying relationship means the parent’s key is part of the child’s key. The child is not merely associated with the parent; the parent helps identify the child.
In the building and room example, building_id is both a foreign key to building and part of the primary key of room. That makes the relationship identifying.
This is different from a child table that simply stores a foreign key for reference. For example, an employee may have a department_id, but employee_id may still identify the employee globally. In that case, department helps describe or organize the employee, but it does not define the employee’s identity.
A foreign key says one record refers to another. An identifying relationship says the referenced record is part of the child’s identity.
The relational table shape
The usual relational pattern is straightforward:
- The parent table has its own primary key.
- The weak child table includes the parent key as a foreign key.
- The weak child table also includes a partial key that is unique only within the parent.
- The child table’s primary key is the combination of parent key and partial key.
For rooms, that means building has primary key building_id. Room has building_id and room_number. The primary key of room is the composite key of building_id and room_number.
This design states the rule directly in the schema: a room number may repeat across buildings, but it may not repeat within the same building.
| Table | Key design | Meaning |
|---|---|---|
| building | Primary key: building_id | Each building has a standalone identity. |
| room | Primary key: building_id plus room_number | A room number is unique only inside its building. |
| room | Foreign key: building_id references building | The parent building participates in the room’s identity. |
Counterexamples: when not to model a weak entity
Not every child-looking table is a weak entity. A record can belong to a parent without depending on that parent for identity.
If each room had a permanent global room_id assigned by the facilities system, then room_id could identify the room by itself. The room would still reference building_id, but building_id would not need to be part of the primary key.
Another counterexample is a product assigned to an order line. A product may appear on many order lines, but the product’s identity does not depend on the order. The order line may be parent-dependent; the product is not.
| Situation | Weak entity? | Reason |
|---|---|---|
| Invoice line numbered 1, 2, 3 within each invoice | Yes | Line number is only unique inside one invoice. |
| Room 101 reused across multiple buildings | Yes | Room number needs building_id to identify one room. |
| Employee with globally assigned employee_id | Usually no | The employee can be identified without department_id. |
| Product with globally assigned sku | No | The product identity does not depend on the order where it appears. |
| File name unique only within a parent folder | Often yes | The same file name may exist in different folders. |
Composite key or surrogate key
A weak entity does not force one physical key style in every system. The modeling decision and the implementation decision are related, but not identical.
A composite primary key such as building_id plus room_number makes the dependency visible and enforceable. It is clear to readers of the schema that room_number is scoped by building.
A surrogate key such as room_id can still be used, but it should not erase the business rule. If room_id is the primary key, the table should still usually enforce uniqueness on building_id plus room_number. Otherwise the model allows duplicate rooms inside the same building even though the business rule says it should not.
The operator rule is: if you add a surrogate key for convenience, keep the natural identifying constraint that protects the meaning of the data.
A surrogate key can make joins easier, but it does not replace the need to enforce the real-world uniqueness rule that defines the weak entity.
Deletion and update semantics
Weak entities also force you to be clear about lifecycle. If a building is removed from the operational model, what should happen to its rooms? If an invoice is removed, what should happen to its invoice lines?
The answer is a business rule, not a default. Some child records should be deleted with the parent. Others should be retained for audit history, closed-period reporting, or regulatory reasons. The identifying relationship tells you the child depends on the parent for identity, but it does not automatically tell you the retention policy.
Be especially careful with parent key changes. If the parent key is part of the child key, changing the parent key can imply changing child identities too. In well-designed systems, primary keys are usually chosen to be stable enough that this is rare.
Common failure modes
The most common failure is treating a partial key as if it were globally unique. This creates accidental collisions, incorrect joins, and duplicate handling that looks arbitrary to users.
A second failure is hiding the dependency behind a surrogate key without enforcing the scoped uniqueness rule. The table looks clean, but it permits invalid data.
A third failure is moving the parent key into a nullable reference column. A weak entity should not float without the parent that identifies it. If the child can exist without the parent, either the entity may not be weak, or the model is missing a separate lifecycle state.
A fourth failure is using labels as identifiers. Names such as Basement, Default, Main, or Archive often repeat. If a label is only unique inside a parent, model it as a partial key or attribute scoped by that parent, not as a global identity.
If analysts must always add the parent table to make a child identifier unambiguous, the model probably contains a parent-dependent identity whether or not the schema admits it.
Decision checklist for practitioners
Use this checklist before you create the table:
- Can the child record be uniquely identified without the parent key?
- Is the child’s local identifier reused under different parents?
- Would two parents be allowed to have children with the same local number, name, or sequence?
- Does the parent key need to be included in joins to prevent ambiguity?
- If using a surrogate key, will you still enforce uniqueness on the parent key plus the partial key?
- Is the child allowed to exist before, after, or without the parent?
- Are delete, archive, and history rules explicit rather than assumed?
If the child cannot be identified without the parent, model the parent dependency directly. If the child has a stable global identifier, use the parent relationship for context rather than identity.
Key takeaways
- A weak entity is a record whose identity is incomplete without a parent record.
- An identifying relationship puts the parent key inside the child’s primary key or equivalent uniqueness constraint.
- The child-side value is often a partial key, such as line number, room number, chapter number, or local name.
- A surrogate key can be useful, but it should not remove the constraint that enforces parent-scoped uniqueness.
- Do not confuse ordinary foreign-key reference with identity dependency.
Next step
Take one child table in your current model and test whether its identifier is globally unique or only unique within a parent. If it is only unique within a parent, write down the parent key plus partial key that actually identifies one record, then check whether the database enforces that rule.
- Read Associative Entities for Many-to-Many Relationships: How to model relationship attributes without flattening two entity tables into a misleading shape.
- Read Entity Identity Versus Record Identity: A practical guide to separating a business thing from the many rows that describe, observe, or mention it.
- Read Data Modeling Before Dashboards: Build Metrics People Can Trust: Use business definitions, entities, events, and trusted marts before investing in dashboard polish.