Rules That Span Rows, Tables, and Time

Learning outcomes

Some of the most important business rules cannot be checked by looking at one row. They depend on multiple rows, multiple tables, workflow states, concurrent transactions, or time intervals. These are still database-design concerns because the schema must make them enforceable and observable.

What makes a rule “span” data?

A rule spans data when its truth cannot be determined from one row alone. Examples:

• one active primary assignment per work order;
• no overlapping bookings for the same room;
• account balance cannot go below an allowed limit;
• every closed work order must have at least one completed assignment;
• one verified email cannot belong to two active accounts;
• a technician cannot be scheduled for overlapping shifts.

Cross-row uniqueness

“One current primary assignment per work order” may be expressible with a partial unique index in a DBMS that supports it:

CREATE UNIQUE INDEX ux_one_primary_open_assignmentON work_order_assignment(work_order_id)WHERE role_code = 'primary'  AND ended_at IS NULL;

This is stronger than application code that merely checks first and inserts second.

The check-then-insert race

Imagine two concurrent requests:

1. Transaction A checks: no primary assignment exists.
2. Transaction B checks: no primary assignment exists.
3. A inserts one.
4. B inserts another.

Both application checks passed, but the final database violates the rule. A database uniqueness constraint or correct transaction isolation is needed.

Temporal overlap rules

A technician should not have two assignments whose active intervals overlap:

[start1, end1) overlaps [start2, end2)

This is not a single-row check because the new interval must be compared with existing intervals for the same technician.

Exclusion constraints and database-specific features

Some databases provide exclusion constraints or range types that can enforce non-overlap elegantly. Others require triggers or transaction logic. The exact mechanism is physical-design-specific, but the data model should identify the invariant explicitly.

Cross-table rules

Suppose a WorkOrder can transition to closed only when:

• all required repair tasks are complete;
• at least one technician assignment exists;
• all consumed parts are recorded;
• no active assignment remains open.

This requires reading multiple tables and usually belongs in one transactional workflow operation.

State-transition rules

Not every status change is valid:

open -> scheduledscheduled -> in_progressin_progress -> closedopen -> cancelledclosed -> open   [maybe forbidden]

A status column plus check constraint validates allowed values, but it does not validate transitions from the previous state.

Model transitions explicitly when needed

If transition history matters, create WorkOrderStatusHistory:

WorkOrderStatusHistory(  status_history_id,  work_order_id,  from_status,  to_status,  changed_at,  changed_by)

Then transition rules and auditability become easier to reason about.

Aggregate invariants

Examples:

• sum of allocation percentages on active assignments must not exceed 100%;
• invoice payments must not exceed amount due unless overpayment is allowed;
• inventory reservations must not exceed available stock;
• team capacity cannot exceed a threshold.

These rules depend on sets or aggregates and are sensitive to concurrency.

Triggers: powerful but deliberate

Triggers can enforce cross-row and cross-table rules near the data, but they introduce hidden execution paths and can become difficult to reason about if overused. Use them when:

• the invariant must hold for every writer;
• the DBMS provides no simpler declarative constraint;
• the logic can be kept small, deterministic, and well tested.

Service-layer transactions

Complex workflows often belong in application/service logic wrapped in a transaction:

BEGINvalidate work order statevalidate assignmentsvalidate required tasksupdate statusinsert status historywrite audit eventCOMMIT

The transaction ensures all related changes succeed or fail together.

Deferred constraints

Some invariants are temporarily violated during a multi-step transaction but must hold at commit. Database systems that support deferrable constraints can be useful for such cases. The model should distinguish “must hold after every statement” from “must hold when the transaction commits.”

Asynchronous validation

Not every quality rule can or should block writes. Examples such as “customer address should geocode successfully” may require external services. These can be modeled as asynchronous validation states:

address_validation_statuslast_validated_atvalidation_error_code

The schema still makes quality status visible.

Temporal integrity

Rules involving effective dates may include:

• ownership periods for one Asset must not overlap;
• only one active price per Part for a given currency/date;
• one Employee cannot have two primary departments at the same instant;
• retired reference codes cannot be selected for new transactions.

These require explicit time intervals and well-defined boundary semantics such as closed-open intervals [start, end).

Schema design can simplify enforcement

A difficult invariant may signal that the schema grain is wrong. For example, embedding current and historical ownership in one Asset row makes temporal rules awkward. Introducing AssetOwnership with one row per ownership period gives the rule a clear place to live.

WorkshopHub invariant catalogue

Testing complex constraints

Tests should include:

• valid cases;
• boundary values;
• duplicate attempts;
• overlap edge cases;
• concurrent transactions;
• rollback behavior;
• legacy/import paths that bypass normal UI flows.

Chapter 6 checkpoint

Summary and next chapter

Chapter 6 established integrity from the smallest value domain to cross-table temporal invariants. You can now distinguish SQL types from domains, preserve entity and referential integrity, use row-level checks and defaults, and recognize when rules require transactional or specialized database enforcement. Chapter 7 introduces functional dependencies—the formal language that explains why certain schemas produce redundancy and update anomalies.

References

• C. J. Date, Database Design and Relational Theory.
• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• PostgreSQL documentation for unique indexes, exclusion constraints, transactions, and triggers.
• Martin Kleppmann, Designing Data-Intensive Applications.