Hybrid Relational and Document Designs

Learning outcomes

Real systems are rarely purely relational or purely document-oriented. A robust hybrid design keeps core business identity, relationships, and invariants relational while using flexible documents for data whose shape truly varies. The goal is not to mix technologies for novelty, but to place each kind of fact where its semantics are clearest and safest.

Hybrid principle

Relational core:identityforeign keystransactionsbusiness constraintscommon searchable attributesFlexible document:sparse metadataexternal payloadsvariant formsdevice-specific details

Example: WorkOrder inspection

WorkOrder(  work_order_id,  asset_id,  status_code,  opened_at)InspectionSubmission(  submission_id,  work_order_id,  form_version_id,  submitted_at,  answers_json)

Core workflow remains relational; form answers can vary by service type/version.

Version the flexible boundary

Always preserve the schema/form version used to produce the document. Otherwise historical documents become difficult to interpret after fields change.

Promote important fields

If safety_passed becomes critical to closing WorkOrders, do not bury it only inside JSON. Promote or derive it into a constrained relational field used by the transaction.

Relational envelope pattern

DiagnosticCapture(  capture_id PK,  work_order_id FK,  source_system,  schema_version,  captured_at,  severity_code,  payload_json)

The envelope contains stable metadata; payload contains variant details.

Ownership

Decide whether the document is:

• authoritative source;
• immutable source snapshot;
• derived projection;
• cache;
• transport envelope.

These roles imply different update rules.

Queryability

For document fields that become common query predicates, choose among:

• expression/path indexes;
• generated columns;
• promoted columns;
• separate relational tables.

Generated-column bridge

Some databases allow:

temperature_c GENERATED FROM payload_json

so the flexible payload remains while a typed/queryable projection exists.

Consistency across two stores

If relational data is in one database and documents in another, a single local transaction may not cover both. Patterns include:

• outbox/event propagation;
• idempotent consumers;
• eventual consistency;
• compensation/reconciliation.

Do not split stores without a workload reason

If one relational database supports JSON well enough, keeping both core and flexible payload in the same transaction can be simpler than introducing a second datastore.

Document store as projection

A search/document database can be a derived projection:

relational source    |  outbox    |document/search projection

This keeps source-of-truth semantics clear.

Document duplication and Chapter 13

All denormalization rules still apply:

• one owner;
• freshness SLA;
• idempotency;
• reconciliation;
• rebuildability.

Security and privacy

Flexible documents can accidentally collect ungoverned personal data. Define allowed fields, classification, retention, and masking rules. Chapter 17 develops this further.

Schema drift monitoring

Track unexpected keys/types in document payloads. Otherwise producers can silently invent new fields that downstream systems interpret inconsistently.

Migration strategy

When JSON schema version changes:

1. accept old and new versions during transition;
2. write new version;
3. backfill or transform old documents if necessary;
4. update readers;
5. retire old version intentionally.

WorkshopHub hybrid map

Decision framework

1. Does the fact have independent identity?
2. Does it participate in foreign keys?
3. Does it require typed constraints?
4. Is it queried frequently across rows?
5. Does shape vary substantially?
6. Is the payload externally defined?
7. Must it be preserved exactly as received?
8. Can the structure be versioned and validated?

Practice: choose the boundary

Chapter 15 synthesis

Summary and next chapter

Chapter 15 showed how to use semi-structured flexibility without abandoning relational discipline. You can now place JSON safely, manage schema-on-write versus flexible attributes, avoid unsafe polymorphic foreign keys, evaluate EAV realistically, and design hybrid relational/document systems. Chapter 16 moves into schema evolution: backward-compatible changes, expand-and-contract migrations, backfills, zero-downtime rollout, versioning, rollback, and database changes in Git.

References

• Martin Kleppmann, Designing Data-Intensive Applications.
• Bill Karwin, SQL Antipatterns.
• Martin Fowler, writings on persistence patterns and schema evolution.
• DBMS documentation for JSON, generated columns, and indexing.