Implementing Weak Entities and Entity Hierarchies | Databases

Weak entities

A weak entity is one that cannot be uniquely identified by its own attributes alone. It depends on an identifying (owner) entity. In the E/R model, a weak entity is represented by a double-lined rectangle; its identifying relationship is represented by a double-lined diamond.

Implementation pattern

Let $S(\underline{Z}, W)$ be the identifying entity and $T$ be the weak entity. The weak entity table is:

$T(\underline{Z, \text{DISC}}, Y)$

where:

$Z$ is a foreign key to $S$ (the identifying entity).
DISC is the discriminator — a partial key that, combined with $Z$ , uniquely identifies each instance of $T$ .
$Y$ are the weak entity’s own attributes.

The primary key of $T$ is the composite $(Z, \text{DISC})$ .

Example: university modules

A university offers courses. Each course has modules. A module number like “Databases” is only meaningful within the context of a specific course.

E/R model:

Strong entity: Course(course_id, course_name)
Weak entity: Module(number, title, credits), identified by Course via the Includes relationship.

Relational schema:

CREATE TABLE courses (
  course_id   INTEGER     NOT NULL,
  course_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (course_id)
);

CREATE TABLE modules (
  course_id   INTEGER      NOT NULL REFERENCES courses (course_id),
  number      VARCHAR(16)  NOT NULL,
  title       VARCHAR(255) NOT NULL,
  credits     INTEGER      NOT NULL,
  PRIMARY KEY (course_id, number)
);

The number discriminator alone is not unique (multiple courses could each have a “module 4”), but (course_id, number) is unique. The foreign key course_id links each module to its identifying course.

Alternative: expansion into one table

If the weak entity has a single attribute set (one row per owner-discriminator pair), we can expand the identifying entity:

$R(\underline{Z}, W, \text{DISC}, U, Y)$

where $U$ and $Y$ would otherwise have been the attributes of $T$ . This avoids a join but reintroduces possible NULL values for $Z$ values that do not have an associated weak entity instance. The choice between a separate table and expansion follows the same trade-off as 1:1 or 1:M relationships (see the previous note).

Entity hierarchies

An entity hierarchy (ISA relationship) occurs when one entity type is a specialisation of another. For example, Employee is a general entity type; Manager and Engineer are specialisations.

Approach 1: One table per entity (normalised)

Create a table for each entity in the hierarchy, including the parent:

Employee(person_id, name, hire_date)
Manager(person_id, budget)
Engineer(person_id, specialism)

The child tables include only the parent’s key and their own specialised attributes. The foreign key constraint $\pi_{\text{person\_id}}(\text{Manager}) \subseteq \pi_{\text{person\_id}}(\text{Employee})$ ensures that every manager is also an employee.

To retrieve all attributes of a manager, join:

SELECT e.person_id, e.name, e.hire_date, m.budget
FROM employee e JOIN manager m ON e.person_id = m.person_id;

Advantages:

No NULL values. Every column is meaningful for every row.
Constraints are precise: you cannot accidentally assign a budget to a non-manager.
Adding a new sub-type creates a new table without altering existing ones.

Disadvantages:

Requires JOINs to retrieve the full set of attributes for a sub-type entity.
Ensuring an entity belongs to at most one sub-type (disjointness) requires application-level logic or triggers.

Approach 2: Single table with type tags (denormalised)

Place all attributes of all entities in the hierarchy into a single table with a discriminator column:

CREATE TABLE employee (
  person_id   INTEGER      NOT NULL PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  hire_date   DATE         NOT NULL,
  budget      DECIMAL,        -- NULL for non-managers
  specialism  VARCHAR(255),   -- NULL for non-engineers
  emp_type    VARCHAR(16)  NOT NULL CHECK (emp_type IN ('manager', 'engineer'))
);

The emp_type column acts as a type discriminator. Rows where emp_type = 'manager' will have budget populated and specialism set to NULL.

Advantages:

Simple queries: one table, no JOINs.
Easy to list all employees regardless of type.

Disadvantages:

Many NULL columns waste space.
Schema-level constraints cannot express “managers must have a budget” or “engineers must have a specialism”. These rules move to application code.
Adding a new sub-type requires ALTER TABLE to add new columns.
The type tag introduces redundancy: specialism IS NULL can be deduced from emp_type = 'manager'.

Approach 3: One table per concrete sub-type (no parent table)

Create a table for each concrete sub-type, with all inherited columns duplicated:

CREATE TABLE manager (
  person_id   INTEGER      NOT NULL PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  hire_date   DATE         NOT NULL,
  budget      DECIMAL      NOT NULL
);

CREATE TABLE engineer (
  person_id   INTEGER      NOT NULL PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  hire_date   DATE         NOT NULL,
  specialism  VARCHAR(255) NOT NULL
);

There is no Employee parent table. Every manager and engineer row carries all inherited columns.

Advantages:

No JOINs needed for any query.
No NULL columns.
Each table is self-contained.

Disadvantages:

Duplication of inherited columns.
Queries over all employees (regardless of type) require UNION:

SELECT person_id, name, hire_date FROM manager
UNION
SELECT person_id, name, hire_date FROM engineer;

No single place to enforce a global constraint across all sub-types (e.g., unique name across managers and engineers).
Adding a new sub-type requires a new table with all inherited columns.

Multi-level hierarchies

For a hierarchy with more than two levels (e.g., Person → Employee → Manager → SeniorManager), approaches 1 and 3 scale better. Approach 2 (single table) becomes unwieldy as columns proliferate for each level of specialisation.

Comparison

Criterion	Approach 1 (one per entity)	Approach 2 (single table)	Approach 3 (one per concrete type)
NULL values	None	Many	None
JOINs needed	Yes (for sub-type queries)	No	No (for sub-type queries); `UNION` for parent queries
Schema evolution	Easy (add new table)	Hard (`ALTER TABLE`)	Medium (new table with all columns)
Constraint enforcement	Strong (FK constraints between tables)	Weak (logic in application)	Medium (check constraints per table)
Storage efficiency	Good	Poor (`NULL` columns)	Moderate (duplication of inherited columns)
Query over all types	`UNION` across all tables	Simple	`UNION` across all tables

Choosing an approach

Approach 1 is the default for well-normalised schemas. It is the most flexible and enforces the most constraints.
Approach 2 can be acceptable for small, stable hierarchies where query simplicity matters more than storage efficiency.
Approach 3 is appropriate when sub-types differ substantially in their attributes and you rarely query across all sub-types.

In practice, the relational model favours approach 1: it aligns with normalisation principles and keeps the schema maintainable as it evolves.

Summary

Weak entities depend on an identifying entity. Their table includes a foreign key to the owner and a discriminator. The primary key is (owner_key, discriminator).
Entity hierarchies can be implemented with one table per entity (normalised), a single table with type tags (denormalised), or one table per concrete sub-type.
Approach 1 is the cleanest: no NULLs, strong constraints, easy evolution.
Approach 2 is simpler to query but wastes space and weakens constraints.
Approach 3 avoids NULLs and JOINs for sub-type queries but requires UNION for parent-type queries and duplicates columns.