Mandatory and Optional Participation

Learning outcomes

Maximum cardinality says whether the upper bound is one or many. Participation adds the lower bound: may an entity instance exist without participating in the relationship, or must it participate at least once? This is commonly expressed as optional versus mandatory participation, or minimum cardinality 0 versus 1.

Minimum and maximum cardinality together

A relationship endpoint is often described using a pair such as:

• 0..1 — optional, at most one;
• 1..1 — mandatory, exactly one;
• 0..* — optional, any number;
• 1..* — at least one, possibly many.

These pairs are more precise than saying only “one-to-many.”

WorkshopHub example: Asset and WorkOrder

Suppose the rules are:

• An Asset may exist before it has ever been repaired.
• Every WorkOrder must refer to exactly one Asset.

Then:

The relationship is one-to-many in maximum cardinality, but participation differs at each end.

Mandatory participation through NOT NULL foreign keys

For WorkOrder, “exactly one Asset” maps naturally to a non-null foreign key:

asset_id INTEGER NOT NULL,FOREIGN KEY (asset_id) REFERENCES asset(asset_id)

NOT NULL prevents a WorkOrder from omitting the relationship. The foreign key prevents it from pointing to a nonexistent Asset.

Optional participation through nullable foreign keys

Suppose a work order may exist before a primary technician is assigned:

primary_technician_id INTEGER NULL

A nullable foreign key can represent 0..1 participation if the model stores only the current primary technician. But if assignment history or multiple technicians are required, the correct model is an assignment entity rather than a single nullable column.

Parent optionality is different from child mandatory participation

Consider Customer–Asset:

• A new Customer may have zero Assets.
• Every Asset must have one current Customer owner.

This means Customer participation is optional while Asset participation is mandatory. Beginners sometimes mistakenly think a mandatory foreign key means every parent must have children. It does not.

Some mandatory participation rules span multiple rows

“Every WorkOrder must eventually have at least one Technician assignment” cannot be enforced by placing NOT NULL on one WorkOrder column when assignments live in a child table. A foreign key on WorkOrderAssignment ensures each assignment references an order; it does not guarantee each order has an assignment.

Possible enforcement strategies include:

• allowing orders to exist unassigned in early states, then enforcing the rule during a state transition;
• using transactional service logic;
• using deferred constraints or database-specific triggers where appropriate;
• validating through scheduled data-quality checks if the rule is operational rather than immediate.

Existence dependency

An entity has an existence dependency when it cannot meaningfully exist without another entity. WorkOrderAssignment cannot exist without both its WorkOrder and Technician. PartUsage cannot exist without a WorkOrder and Part.

This usually implies mandatory foreign keys:

work_order_id INTEGER NOT NULL REFERENCES work_order,technician_id INTEGER NOT NULL REFERENCES technician

Deletion rules then become important: should deleting a WorkOrder cascade to its assignments, be prohibited, or never happen because orders are retained historically?

Participation changes with lifecycle state

A rule may not be globally mandatory but may become mandatory in a particular state.

This is more complex than static nullability. The rule belongs to the workflow and transaction boundary, not merely the column definition.

Optional does not mean unimportant

An optional fact can still be important. closed_at is optional while an order is open but essential once it closes. A customer's secondary phone is optional but may be operationally useful.

Optionality should describe valid states, not developer convenience. Avoid making columns nullable merely because the application has not decided when to collect them.

Unknown versus not applicable

Participation can reveal different forms of absence. If an Asset has no assigned warranty provider, does that mean:

• the asset is definitely not under warranty;
• the warranty provider is unknown;
• warranty does not apply to this asset category;
• the provider has not yet been entered?

A nullable foreign key collapses these possibilities. If the distinction matters, model a status or lifecycle explicitly.

Deletion and participation

Referential actions should follow lifecycle semantics. Consider deleting a Customer who still owns Assets. Options include:

• RESTRICT deletion until ownership is transferred;
• CASCADE deletion of assets, usually dangerous for operational history;
• SET NULL, invalid if every Asset must have an owner;
• soft-delete or deactivate Customer while retaining historical relationships.

Participation rules therefore influence deletion strategy.

SQL example

CREATE TABLE asset (    asset_id     INTEGER PRIMARY KEY,    customer_id  INTEGER NOT NULL,    serial_number TEXT,    FOREIGN KEY (customer_id)      REFERENCES customer(customer_id)      ON DELETE RESTRICT);CREATE TABLE work_order (    work_order_id INTEGER PRIMARY KEY,    asset_id      INTEGER NOT NULL,    status        TEXT NOT NULL,    FOREIGN KEY (asset_id)      REFERENCES asset(asset_id)      ON DELETE RESTRICT);

The schema says every Asset needs a Customer and every WorkOrder needs an Asset. It does not say every Customer needs an Asset or every Asset needs a WorkOrder.

Practice: minimum and maximum pairs

Summary and next lesson

Participation adds minimum cardinality to the relationship model. Optionality and mandatory participation often map to nullability and foreign keys, but state-dependent and cross-row rules require transactional or richer constraint logic. The next lesson explores relationships whose structure is more complex than a simple pair of different entity types.

References

• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• Thomas Connolly and Carolyn Begg, Database Systems.
• PostgreSQL and SQLite documentation for foreign keys and referential actions.